Sequencing Identity Numbers in SQL Server 2012

Identity columns are nothing new in T-SQL.  They are great when you want a unique number to be automatically assigned every time you insert a new record.  The trouble is that while they are unique within the table, they are probably the same between tables.  Every table with an identity column has a 1 for the first record, a 2 for the second, etc.  Of course you can set the seed and increment for the identity column to something other than (1,1), but you still have to manually track which table has which seed.  Trying to track that is destined for failure.  What we really need is a way to have automatically assigned numbers for new records, but ones which are guaranteed to be unique across any table that uses them.  Of course, you know I wouldn’t be writing this if there weren’t a solution.  In SQL Server 2012, Microsoft introduced something called the SEQUENCE object that does this very thing.

ENTER THE SEQUENCE…

There are two steps involved here.  First we create the SEQUENCE object, and then we use it when we insert records.  So, let’s say I am creating a database to track my cigars.  I have two tables, one tracks my Cubans and the other tracks all the others.  I need to keep my Cubans in a separate table so that if the Bureau of Alcohol, Tobacco, Firearms and Explosives ever investigates my system I can easily drop that table.  (Have you ever wondered what Alcohol and Tobacco have to do with Firearms and Explosives?  I guess that’s a topic for a different kind of blog.)   Anyway, I want to number my individual cigars and I want them to be kept in order, whichever table they are in.  Sounds like a job for a SEQUENCE object.  So, let’s create the tables:

USE CIGARS
GO

CREATE TABLE stogies.Legal (CigarID int, Brand nvarchar(50), Rating tinyint)
CREATE TABLE stogies.Cuban (CigarID int, Brand nvarchar(50), Rating tinyint)
GO

Now, let’s create the SEQUENCE .  Of course you can open Management Studio and use the GUI, but that’s no fun; we want to write some code.

CREATE SEQUENCE stogies.CountEm AS int
START WITH 100 INCREMENT BY 10
GO

So now, all that’s left to do is insert some records.  To insert a value into my CigarID column, I will use the NEXT VALUE FOR function to generate a new unique number.  It will look something like this:

INSERT stogies.Legal VALUES (NEXT VALUE FOR stogies.CountEm, ‘MonteCristo’, 7)
INSERT stogies.Cuban VALUES (NEXT VALUE FOR stogies.CountEm, ‘AVO’, 10)
INSERT stogies.Legal VALUES (NEXT VALUE FOR stogies.CountEm, ‘La Gloria’, 8)
INSERT stogies.Cuban VALUES (NEXT VALUE FOR stogies.CountEm, ‘Hoyo de Moterey’, 9)

Now, if I query the two tables, I get the following output:
SELECT * FROM stogies.Legal

SELECT * FROM stogies.Cuban

Words of caution: according to BOL, “Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.”  In other words, they are generated anytime you call the NEW VALUE FOR function, and never re-used.  That is true even if you do this (which would return 140):

SELECT NEW VALUE FOR stogies.CountEm

OK, there is one major error in this article, and it has nothing to do with coding or sequencing.  If you find the error, send me an email (jeff.rathjen@quickstart.com) and you will win a brownie point.  Or better yet, come see me in class sometime.