Sequence

Overview

Introduced in SQL Server 2012
User-defined object which generates sequence of numbers according to the property (Ascending/Descending/Cycle, etc..)
It can be defined as any Integer datatype, if not specified, default is BigInt

Sequence vs Identity

	Identity	Sequence
Association	With table	with database, can be used for multiple tables
Generation	generated only when we insert the data in table	can be generated even before inserting data using NEXT VALUE FOR
Cycling	not feasible	feasible, will restart the number once specified number is reached
Reserving number	not feasible, if we insert data from multiple places, it will take the number whatever is next	feasible, we can reserver 5 sequential numbers before inserting the data and later we can use them
Gaps	Generally no unless we have deliberately changed the Identity number, otherwise records will be in a sequence without gaps	One of the purpose of sequence is for multiple tables, gaps are usual

Usage

• Creating a sequence

CREATE SEQUENCE Schema.SequenceName

    AS int

    START WITH 1

    INCREMENT BY 1 ;

• Using sequence while inserting data

--Create the Test schema

CREATE SCHEMA Test ;

GO

-- Create a table

CREATE TABLE Test.Orders

    (OrderID int PRIMARY KEY,

    Name varchar(20) NOT NULL,

    Qty int NOT NULL);

GO

-- Create a sequence

CREATE SEQUENCE Test.CountBy1

    START WITH 1

    INCREMENT BY 1 ;

GO

-- Insert three records

INSERT Test.Orders (OrderID, Name, Qty)

    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;

INSERT test.Orders (OrderID, Name, Qty)

    VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;

INSERT test.Orders (OrderID, Name, Qty)

    VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;

GO

• Calling NEXT VALUE FOR before inserting the data

DECLARE @NextID int ;

SET @NextID = NEXT VALUE FOR Test.CountBy1;

-- Some work happens

INSERT Test.Orders (OrderID, Name, Qty)

    VALUES (@NextID, 'Rim', 2) ;

• Using Sequence in multiple tables

CREATE SCHEMA Audit ;

GO

CREATE SEQUENCE Audit.EventCounter

    AS int

    START WITH 1

    INCREMENT BY 1 ;

GO

CREATE TABLE Audit.ProcessEvents

(

    EventID int PRIMARY KEY CLUSTERED

        DEFAULT (NEXT VALUE FOR Audit.EventCounter),

    EventTime datetime NOT NULL DEFAULT (getdate()),

    EventCode nvarchar(5) NOT NULL,

    Description nvarchar(300) NULL

) ;

GO

CREATE TABLE Audit.ErrorEvents

(

    EventID int PRIMARY KEY CLUSTERED

        DEFAULT (NEXT VALUE FOR Audit.EventCounter),

    EventTime datetime NOT NULL DEFAULT (getdate()),

    EquipmentID int NULL,

    ErrorNumber int NOT NULL,

    EventDesc nvarchar(256) NULL

) ;

GO

CREATE TABLE Audit.StartStopEvents

(

    EventID int PRIMARY KEY CLUSTERED

        DEFAULT (NEXT VALUE FOR Audit.EventCounter),

    EventTime datetime NOT NULL DEFAULT (getdate()),

    EquipmentID int NOT NULL,

    StartOrStop bit NOT NULL

) ;

GO

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)

    VALUES (248, 0) ;

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)

    VALUES (72, 0) ;

INSERT Audit.ProcessEvents (EventCode, Description)

    VALUES (2735,

    'Clean room temperature 18 degrees C.') ;

INSERT Audit.ProcessEvents (EventCode, Description)

    VALUES (18, 'Spin rate threashold exceeded.') ;

INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)

    VALUES (248, 82, 'Feeder jam') ;

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)

    VALUES (248, 1) ;

INSERT Audit.ProcessEvents (EventCode, Description)

    VALUES (1841, 'Central feed in bypass mode.') ;

-- The following statement combines all events, though not all fields.

SELECT EventID, EventTime, Description FROM Audit.ProcessEvents

UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents

UNION SELECT EventID, EventTime,

CASE StartOrStop

    WHEN 0 THEN 'Start'

    ELSE 'Stop'

END

FROM Audit.StartStopEvents

ORDER BY EventID ;

EventID	EventTime	Description
1	2013-07-28 06:10:36.673	Start
2	2013-07-28 06:10:36.677	Start
3	2013-07-28 06:10:36.677	Clean room temperature 18 degrees C.
4	2013-07-28 06:10:36.680	Spin rate threashold exceeded.
5	2013-07-28 06:10:36.680	Feeder jam
6	2013-07-28 06:10:36.680	Stop
7	2013-07-28 06:10:36.680	Central feed in bypass mode.

We can also see that we can define NEXT VALUE FOR as a default constraint, so that way we don't need to use it while inserting the data

• Repeating Sequence

CREATE SEQUENCE CountBy5

   AS tinyint

    START WITH 1

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 5

    CYCLE ;

GO

SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;

GO

SurveyGroup	Name
1	sysrscols
2	sysrowsets
3	sysclones
4	sysallocunits
5	sysfiles1
1	sysseobjvalues
2	syspriorities

• Generating Sequence using OVER clause

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product

WHERE Name LIKE '%nut%' ;

• Resetting the sequence

ALTER SEQUENCE Samples.IDLabel

RESTART WITH 1 ;