Every weekend I'm going to post about a few new features in SQL 2012 (note, in the new post category, there are versions for SQL that go up to SQL 2008....this one is for 2012, but I don't believe there's an entry in the version pulldown)

This feature is called the Sequence Object. This is an excerpt from a CoDe article I wrote last year (http://code-magazine.com/articleprint.aspx?quickid=1203051&printmode=true)

Have you ever wanted a table-independent identity generator within a database? Other databases (e.g. Oracle) have had a sequence generator to produce a new integer identity on demand, without association to a specific table. Imagine a scenario where you could create an integer value for every row in every table, where the value is unique across tables - and without the complexity of a uniqueidentifier.

The new sequence generator object in SQL Server 2012 allows database administrators to have a table-independent "factory": one that produces an integer value that will be unique across every table/row in the database. This effectively creates the equivalent of a uniqueidentifer within a database.

CREATE SEQUENCE DemoSequence START WITH 1 INCREMENT BY 1;

SELECT NEXT VALUE FOR DemoSequence      -- Gives us 1...if you execute again, it will give you 2....note, it's thread safe, you can try it from multiple windows/tasks

CREATE TABLE MyTest  (SequenceValue int, Name varchar(50))
go


 insert into MyTest values  
     (next value for DemoSequence, 'Kevin Goff')

If you want to know the current value in a sequence (in the "factory")...

select current_value from sys.sequences where name = 'DemoSequence'

You can use a sequence object in a default value...and it can even be a bigint (in case you need values beyond 2 billion)

CREATE SEQUENCE BigDemoSequence  START WITH 3000000000 INCREMENT BY 1000000
go

create table MyTestBigInt (sequenceint bigint   default next value for BigDemoSequence, Name varchar(50))
 

insert into MyTestBigInt  (Name) values  (  'test1')
insert into MyTestBigInt (Name)  values  (  'test2')

So...in SQL 2012, there usually won't be a need to use a uniqueidentifier, when you need to store unique int values across tables within a database. (Note: if you need to store unique values across rows across databases/database servers, this won't work....the scope of the uniqueness is within the database, which is still better than regular identity columns that are only unique within the table