The new SEQUENCE type in SQL Server 2012 will allows you to create sequential numbers. In general identity columns can be used to uniquely identify records in a table. But if we wants to maintain identity columns across multiple tables, or if we need the identity column before insertion of record into table, we can use SEQUENCE type in SQL Server 2012.

CREATE SEQUENCE CustomSequence AS INT 
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000;
GO

CREATE TABLE TEST1
(
    Id int,
    Col1 varchar(100)
)
GO
CREATE TABLE TEST2
(
    ID int,
    Col2 varchar(100)
)
GO

INSERT INTO TEST1 (Id,Col1)
VALUES (NEXT VALUE FOR CustomSequence,'a')
INSERT INTO TEST2 (Id,Col1)
VALUES (NEXT VALUE FOR CustomSequence,'b')

SELECT * From Test1 
SELECT * From Test2

Output:

Id          Col1
----------- -------------
1           a


ID          Col2
----------- -------------
2           b

• Reference 1  [26 clicks]
• Reference 2  [23 clicks]
• Reference 3  [22 clicks]

Category :	TSQL Enhancements
Release :	All
Tags :	SEQUENCE,Denali, SQL Server 2012, SQL11