Home > SQL Server 2012 > DENALI TSQL – New Feature | SEQUENCES

DENALI TSQL – New Feature | SEQUENCES


As per MS BOL a Sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested.

--// Create a simple Sequence
CREATE SEQUENCE CountBy1
	START WITH 1
	INCREMENT BY 1 ;
GO

-- Assign new ID sorted by First Name:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy1 OVER(ORDER BY FirstName) as New_ID
FROM Person.Person;
GO
-- If you run the above query again, it won't start from 1 but starts after the max value it ended in its first run.

--// Create a Cyclic Sequence
CREATE SEQUENCE CountBy5 AS tinyint
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 5
	CYCLE ;
GO

-- Assign Persons to 5 different sections:
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
	NEXT VALUE FOR CountBy5 OVER(ORDER BY FirstName, LastName) as New_Section
FROM Person.Person;
GO

--// Final cleanup
DROP SEQUENCE CountBy1
DROP SEQUENCE CountBy5
GO

More on MSDN: http://technet.microsoft.com/en-us/library/ff878058%28SQL.110%29.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 401 other followers

%d bloggers like this: