SQL Server 2011 will contain one of the very interesting feature called SEQUENCE. I have waited for this feature for really long time. I am glad it is here finally. SEQUENCE allows you to define a single point of repository where SQL Server will maintain in memory counter.

USE AdventureWorks2008R2
GO
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO


SEQUENCE is very interesting concept and I will write few blog post on this subject in future. Today we will see only working example of the same.

Let us create a sequence. We can specify various values like start value, increment value as well maxvalue.

-- First Run
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
-- Second Run
SELECT NEXT VALUE FOR Seq, c.AccountNumber
FROM Sales.Customer c
GO

Once the sequence is defined, it can be fetched using following method. Every single time new incremental value is provided, irrespective of sessions. Sequence will generate values till the max value specified. Once the max value is reached, query will stop and will return error message.
Msg 11728, Level 16, State 1, Line 2
The sequence object ‘Seq’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

We can restart the sequence from any particular value and it will work fine.

-- Restart the Sequence
ALTER SEQUENCE [Seq]
RESTART WITH 1
GO
-- Sequence Restarted
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO

Let us do final clean up.

-- Clean Up
DROP SEQUENCE [Seq]
GO

There are lots of things one can find useful about this feature. We will see that in future posts. Here is the complete code for easy reference.

USE AdventureWorks2008R2
GO
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO
-- First Run
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
-- Second Run
SELECT NEXT VALUE FOR Seq, c.AccountNumber
FROM Sales.Customer c
GO
-- Restart the Sequence
ALTER SEQUENCE [Seq]
RESTART WITH 1
GO
-- Sequence Restarted
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
-- Clean Up
DROP SEQUENCE [Seq]
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)