SQL SERVER – Denali – Introduction to SEQUENCE – Simple Example of SEQUENCE

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)

About these ads

10 thoughts on “SQL SERVER – Denali – Introduction to SEQUENCE – Simple Example of SEQUENCE

  1. Hello Pinal,

    This is valuable feature addition in MSSQL2k11 . But I just

    wanted to know what will be its advantage over IDENTITY

    feature in MSSQL .

    Regards,
    Nishit

    Like

  2. Pingback: SQL SERVER – 2011 – SEQUENCE is not IDENTITY Journey to SQL Authority with Pinal Dave

  3. Im having a doubt can u please help me to solve over this problem …

    data having in my table

    name type date

    siva 1 27/10/2010
    siva 2 29/10/2010
    siva 3 30/10/2010
    saro 3 28/10/2010
    saro 1 29/10/2010

    but i need result like this

    name old new date

    siva 1 2 29/10/2010
    siva 2 3 30/10//2010
    saro 3 1 29/10/2010

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

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