I really appreciate if you can share the link with your friends via Facebook,twitter,blog etc.

08 March 2011

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]