Oracle sequence but then in MS SQL Server

Question

In Oracle there is a mechanism to generate sequence numbers e.g.;

CREATE SEQUENCE supplier_seq

    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

And then execute the statement

supplier_seq.nextval

to retrieve the next sequence number.

How would you create the same functionality in MS SQL Server ?

Edit: I'm not looking for ways to automaticly generate keys for table records. I need to generate a unique value that I can use as an (logical) ID for a process. So I need the exact functionality that Oracle provides.

Answer 1

There is no exact match.

The equivalent is IDENTITY that you can set as a datatype while creating a table. SQLSERVER will automatically create a running sequence number during insert. The last inserted value can be obtained by calling SCOPE_IDENTITY() or by consulting the system variable @@IDENTITY (as pointed out by Frans)

If you need the exact equivalent, you would need to create a table and then write a procedure to retun the next value and other operations. See Marks response on pitfalls on this.

Answer 2

make the field an Identity field. The field will get its value automatically. You can obtain the last inserted value by calling SCOPE_IDENTITY() or by consulting the system variable @@IDENTITY

The SCOPE_IDENTITY() function is preferred.

Answer 3

As DHeer said there is absolutely no exact match. If you try to build your own procedure to do this you will invariably stop your application from scaling.

Oracle's sequences are highly scalable.

OK, I take it back slightly. If you're really willing to focus on concurrency and you're willing to take numbers out of order as is possible with a sequence, you have a chance. But since you seem rather unfamiliar with t-sql to begin with, I would start to look for some other options when (porting an Oracle app to MSSS - is that what you're doing)

For instance, just generate a GUID in the "nextval" function. That would scale.

Oh and DO NOT use a table for all the values, just to persist your max value in the cache. You'd have to lock it to ensure you give unique values and this is where you'll stop scaling. You'll have to figure out if there's a way to cache values in memory and programmatic access to some sort of lightweight locks- memory locks, not table locks.

Answer 4

I wish that SQL Server had this feature. It would make so many things easier.

Here is how I have gotten around this.

Create a table called tblIdentities. In this table put a row with your min and max values and how often the Sequence number should be reset. Also put the name of a new table (call it tblMySeqNum). Doing this makes adding more Sequence Number generators later fairly easy.

tblMySeqNum has two columns. ID (which is an int identity) and InsertDate (which is a date time column with a default value of GetDate()).

When you need a new seq num, call a sproc that inserts into this table and use SCOPE_IDENTITY() to get the identity created. Make sure you have not exceeded the max in tblIdentities. If you have then return an error. If not return your Sequence Number.

Now, to reset and clean up. Have a job that runs as regularly as needed that checks all the tables listed in tblIdentites (just one for now) to see if they need to be reset. If they have hit the reset value or time, then call DBCC IDENT RESEED on the name of the table listed in the row (tblMySeqNum in this example). This is also a good time to clear our the extra rows that you don't really need in that table.

DON'T do the cleanup or reseeding in your sproc that gets the identity. If you do then your sequence number generator will not scale well at all.

As I said, it would make so many things easier of this feature was in SQL Server, but I have found that this work around functions fairly well.

Vaccano

Answer 5

This might have already been answered a long time ago... but from SQL 2005 onwards you can use the ROW_NUMBER function... an example would be:

select ROW_NUMBER() OVER (ORDER BY productID) as DynamicRowNumber, xxxxxx,xxxxx

The OVER statement uses the ORDER BY for the unique primary key in my case...

Hope this helps... no more temp tables, or strange joins!!

Answer 6

Identity is the best and most scalable solution, BUT, if you need a sequence that is not an incrementing int, like 00A, 00B, 00C, or some special sequence, there is a second-best method. If implemented correctly, it scales OK, but if implemented badly, it scales badly. I hesitate to recommend it, but what you do is:

1. You have to store the "next value" in a table. The table can be a simple, one row, one column table with just that value. If you have several sequences, they can share the table, but you might get less contention by having separate tables for each.
2. You need to write a single update statement that will increment that value by 1 interval. You can put the update in a stored proc to make it simple to use and prevent repeating it in code in different places.
3. Using the sequence correctly, so that it will scale reasonably (no, not as well as Identitiy :-) requires two things: a. the update statement has a special syntax made for this exact problem that will both increment and return the value in a single statement; b. you have to fetch the value from the custom sequence BEFORE the start of a transaction and outside the transaction scope. That is one reason Identity scales -- it returns a new value irrespective of transaction scope, for any attempted insert, but does not roll back on failure. That means that it won't block, and also means you'll have gaps for failed transactions.

The special update syntax varies a little by version, but the gist is that you do an assignment to a variable and the update in the same statement. For 2008, Itzik Ben-Gan has this neat solution: http://www.sqlmag.com/Articles/ArticleID/101339/101339.html?Ad=1

The old-school 2000 and later method looks like this:

UPDATE SequenceTable SET @localVar = value = value + 5 -- change the tail end to your increment logic

This will both increment and return you the next value.

If you absolutely cannot have gaps (resist that requirement :-) then it is technically possible to put that update or proc in side the rest of your trnsaction, but you take a BIG concurrency hit as every insert waits for the prior one to commit.

I can't take credit on this; I learned it all from Itzik.

Answer 7

Check the link

http://stackoverflow.com/questions/3327899/generating-sequence-number-in-csharp/3328198#3328198

Kalpak Luniya