SQL Server 2012: DENALI series: SEQUENCE feature Part 3 – Re-seeding, Restarting, incrementing & Cycling

Hi Friends,

Here is part 3 of my sequence of blogs on SEQUENCE object πŸ™‚

In part 1, I blogged on how SEQUENCE objects in DENALI behave within transactions. You can see that here;

In part 2, I blogged on some basics on how to get started with SEQUENCE object and how to use it in table definitions: you can find that blog here;

In this blog, I want to show you what else you can do with a SEQUENCE object.

Creating a SEQUENCE Object:

USE tempdb
GO
 
 
CREATE SEQUENCE dbo.TestID
AS INT
MINVALUE 1
NO MAXVALUE
START WITH 1;

Generating NEXT value for a SEQUENCE object:

   
SELECT NEXT VALUE FOR dbo.TestID

Re-seeding a SEQUENCE object; note that SEQUENCE is a first class SQL Server object:

ALTER SEQUENCE dbo.TestID
RESTART WITH 20;

Altering a SEQUENCE object to change the increment value by using the INCREMENT clause:

ALTER SEQUENCE dbo.TestID
RESTART WITH 1
INCREMENT BY 10;

Dropping a SEQUENCE object:

DROP SEQUENCE dbo.TestID

Suppose the minimum or maximum value of a SEQUENCE object is reached and you want to re-start it automatically (minimum in case the SEQUENCE object is descending in nature), you can use the CYCLE clause. For example;

CREATE SEQUENCE dbo.KolDevCon2
   AS tinyint
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE ;
GO

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server 2012: DENALI series: SEQUENCE feature Part 3 – Re-seeding, Restarting, incrementing & Cycling”

  1. Hi,

    Is there a way to function to capture sequence value generated for inset record like Ident_current?

Leave a Reply

Your email address will not be published.