For training related to .net,OOPS,design patterns, MVC,WCF and .net fundamentals
contact at sukeshmarla@gmail.com or Click Here

Click here for my training feedbacks

Followers

Follow Us



Share your Interview questions here


For software development queries contact SukeshMarla@Gmail.com

Follow by Email

Learn everything about JSON in a day

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]