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.”
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