Best Explanation of Sequences in SQL Server

Sequences

Sequence is introduced in SQL Server 2012. Sequence is a user-defined object that creates a sequence of a number. It is similar to an identity column.

Characteristics Of Sequences

• It is independent from table
• New value can be obtain before an INSERT statement
• New values can be generate in an UPDATE statement
• For performance point of view, you can set cache size option and also you can define min and max values, can allow cycling
• You can alter current value, increment, min value, max value, cycle and cache size properties of a sequence object besides the data type.

Creating Sequence Syntax:-

CREATE SEQUENCE dbo.Sequence_name  
AS [ sqol-defined_integer_type OR user-defined_integer_type ]
START WITH <constant> 
INCREMENT BY <constant>
MINVALUE <constant>  OR NO MINVALUE
MAXVALUE <constant>  OR NO MAXVALUE
CYCLE 
CACHE <constant> OR  NO CACHE;

START WITH:  Set Starting number in the sequence

INCREMENT BY:  Set incrementing value of the sequence

MINVALUE:  Set minimum value for sequence can produce.

MAXVALUE:  Set maximum value for sequence can produce.

CYCLE:  If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.

CACHE:  If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.

Example:-

Below is CREATE Sequence example

CREATE SEQUENCE dbo.TestSQLSequence AS BIGINT 
START WITH 8
INCREMENT BY 1 
MINVALUE 2 
MAXVALUE 9 
CYCLE 
CACHE 10;

Below is DROP Sequence example

DROP SEQUENCE dbo.TestSQLSequence

Argument 'START WITH' cannot be used in an ALTER SEQUENCE statement. RESTART WITH is used in Place of start with.

Alter SEQUENCE dbo.TestSequence 
RESTART WITH 2 
INCREMENT BY 1 
MINVALUE 2 
MAXVALUE 9 
CYCLE 
CACHE 10; 
GO 

Now Create table

Create Table Account
(
ID INT,
Name NVARCHAR(100) NOT NULL,
DeptId INT NOT NULL 
)

Now Use created Sequence object "dbo.TestSQLSequence" to get the value by using NEXT VALUE FOR

The new NEXT VALUE FOR T-SQL keyword is used to get the next sequential number from a Sequence.

INSERT Account (ID, Name,DeptId) 
VALUES
(
NEXT VALUE FOR TestSQLSequence,
'KPO',
1
);
 
INSERT Account (ID, Name,DeptId) 
VALUES
(
NEXT VALUE FOR TestSQLSequence,
'BPO',
1
);
 
INSERT Account (ID, Name,DeptId) 
VALUES
(
NEXT VALUE FOR TestSQLSequence, 
'VIP',
2
); 
INSERT Account (ID, Name,DeptId)
VALUES 
(
NEXT VALUE FOR TestSQLSequence, 
'CEO',
3
);