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