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.

Edit:
SQL Server has implemented the Sequence similar to the Oracle. Please refer to this question for more details.

How would you implement sequences in Microsoft SQL Server?

Answer 2

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 3

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.