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.

Solarwinds

-- 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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQLAuthority News – Deployment guide for Microsoft SharePoint Foundation 2010
Next Post
SQL SERVER – Denali – SEQUENCE is not IDENTITY

Related Posts

8 Comments. Leave new

  • 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

    Reply
  • Identity is related to one table only however SEQUENCE will be independent from table.

    Look for more blog post on the subject.

    Reply
  • Developer Seer
    February 1, 2011 7:47 pm

    Dave, will SQL 11 use VS 2010 Dev Environment for SSIS, SSRS and SSAS? Is CTP1 already like that?

    Thanks

    Reply
  • Pinal this is really gr8 article.

    Reply
  • good one Pinal..

    Reply
  • This feature is great instead of using functions and temp tables..Great thanks for sharing.

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

    Reply

Leave a Reply

Menu