In SQL Server 2012 there is a new feature that should be very familiar to Oracle folks called SEQUENCE.

(my wife LOVES this game)

“Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.”

MSDN Link for CREATE SEQUENCE

So what did I get from this:

• It’s like Identity but not
• Can reuse values once a limit has been reached
• Sequence can be “shared” between many tables as it is it’s own object
• Has to be assigned to a schema

There are plenty of great examples on how to create and reference a SEQUENCE on the MSDN link above but where I’m struggling is why I would want to use this new functionality?

Some problems/questions I have right off the bat:

• Inserting conflict – what if 2 transactions ask for the next sequence (NEXT VALUE FOR) and both try and commit the same “OrderID”?
• IDENTITY columns can’t be updated “accidentally” … other then the use of a trigger I don’t see how you could prevent an update to a column referencing a sequence
• Reusing sequences just sounds dangerous

I did a quick test of the “Inserting Conflict” issue and created a SEQUENCE:

CREATE SEQUENCE dbo.TestSequence1 AS INT
START WITH 1
INCREMENT BY 1

Then I opened 2 mgmt studio query windows and ran the following:

DECLARE @NextSequence AS INT
 
BEGIN TRAN
SELECT @NextSequence = NEXT VALUE FOR dbo.TestSequence1
 
SELECT @NextSequence

and I’m happy to report that getting the “NEXT VALUE” increments the sequence so as long as you do this within a transaction you should be good to go without worrying about an INSERT conflict.

So where does that leave me?

It leaves me still asking WHY… so where is where the “win a prize” comes in…

HOW TO WIN A PRIZE

In the comments section if you can give me a reason or a use-case as to why/how you would use this over IDENTITY you could win a prize

Now the winner is going to be the reason/use-case/comment that best explains to ME how/why you would use SEQUENCE and why it would be a better solution than an IDENTITY.

Contest end date of: Feb 10th

If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk