SQL Server 2012 - Sequence Object

The Release of SQL Server 2012 introduces the sequence object to the world of SQL Server. A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table.

A few interesting differences between the two are:

• A Sequence object is independent of any table, whereas the Identity column property is table specific
• Unlike Identity, you can generate a new sequence value before using it in a SQL Statement
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
• Unlike Identity, a SQL object will increment its value only when it is explicitly called

A detailed list of differences between Identity and Sequence objects can be found here.

CREATE SEQUENCE dbo.demo_2012_sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
CACHE 20 CYCLE;

-- get one value out of the sequence object at one time
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no;
GO
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS next_seq_no;
GO