Introduction to SEQUENCE - Sql Server 2011

SQL Server 2011 will contain one of the very interesting feature called SEQUENCE that allows us to define a single point of repository where SQL Server will maintain in memory counter.

Example

First Create a sequence First as

CREATE SEQUENCE [BlogSequence]AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000

Once the sequence is defined, it can be fetched using following method.

-- First Run
SELECT NEXT VALUE FOR BlogSequence, b.BlogTitleFROM SukeshMarlaBlogs b
-- Second Run
SELECT NEXT VALUE FOR BlogSequence, b.BlogTitleFROM SukeshMarlaSqlBlogs b

-->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

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

-- Restart the Sequence
ALTER SEQUENCE [BlogSequence]
RESTART WITH 1
Sequence Restarted
SELECT NEXT VALUE FOR BlogSequence, b.BlogTitleFROM SukeshMarlaSqlBlogs b

Final clean up.

-- Clean Up
DROP SEQUENCE [BlogSequence]