What are Sequence Numbers in SQL Server 2012

---

Download xSQL Compare Bundle, starting from $0!

---

If you have worked with SQL Server for a while you either have built yourself or at least seen somewhere some sort of a GetNextValue(MyTable) function that returns the next available sequential ID for the given table. The way it worked was: you would have a small table with two columns (TableName, NextID) and the above function would read the corresponding ID from this table and increment the NextID column. Why was this necessary when you could use the auto incremented Identity column? A couple of reasons: you needed the ID before you inserted the row in the table; you wanted to be able to possibly re-use those IDs' and, you wanted to use the ID as a foreign key on dependent tables. You made this solution work and it was ok however, you had to handle concurrency (multiple processes requesting new IDs for the same table) and this process often became a bottleneck causing unexpected performance issues.

The uniqueidentifier introduced in SQL Server 2005 was great but often that is not what you want, instead of a 50 character long unique identifier you want a sequential number.

SQL Server 2012 addresses all those problems with a new user-defined schema-bound object called sequence that generates a sequence of numeric values according to a set of specifications. Here is how it works:

• first, you create a sequence that you intend to use for a certain purpose as

         CREATE SEQUENCE MySchema.MyFirstSequence

            AS int  /* can be any built in integer type or a user defined integer type */

            START WITH 1

            INCREMENT BY 1 ;

• Whenever we need to grab the next number in the sequence we simply refer to this object as NEXT VALUE FOR MySchema.MyFirstSequence

Here are a couple of examples of using the sequence object we created above:

 

Example 1: INSERT INTO MyTable (ID, SomeValue) VALUES (NEXT VALUE FOR MySchema.MyFirstSequence, 'some value')

Example2: Let's say we need to create a record in MyTable and also a couple of dependent records on MyDependentTable where the MyTable.ID participates as a foreign key. In that case we need to first grab the next ID and then use it in our insert statements, so we would do something like this:
DECLARE @nextID int
SET @nextID = NEXT VALUE FOR MySchema.MyFirstSequence
 INSERT INTO MyTable (ID, SomeValue) VALUES (@nextID, 'some value')
 INSERT INTO MyDependentTable (MyTableID, OtherColum) VALUES (@nextID, 'other value 1')
 INSERT INTO MyDependentTable (MyTableID, OtherColum) VALUES (@nextID, 'other value 1')

Here are a few things to note:

• you don't have to worry about concurrency, SQL Server takes care of that
• sql server also takes care of the performance as well - unlike your home build "next sequential value" mechanism the sequence object is unlikely to ever cause any issues for you. 
• you can set min and max values for the sequence and you can cycle through the values in that range
• important: the sequence numbers are generated outside the scope of the current transaction and they are "consumed" even if the transaction that requested the next value is rolled back

For a thorough explanation of the sequence object, the syntax for creating, dropping and altering a sequence, as well as examples of using the sequence object you can check out those two msdn articles: http://msdn.microsoft.com/en-us/library/ff878058.aspx  and http://msdn.microsoft.com/en-us/library/ff878091.aspx

Note: the next version of our schema compare tool, xSQL Object, that will be released next month will include support for the sequence object as well.