This article basically focuses towards the introduction of Sequences with extensive list of examples. To know about Sequence Limitations and Sequence Cache Management and internals you may like to visit the articles: Sequence Limitations and restrictions with Extensive List of examples and Sequence Cache Management and Internals with examples.

SEQUENCE is one of the new feature introduced in Sql Server 2012. Sequence is a user-defined object and as name suggests it generates sequence of numeric values according to the properties with which it is created. It is similar to Identity column, but there are many difference between them. Some of the major differences between them are:

• Sequence is used to generate database-wide sequential number, but identity column is tied to a table.
• Sequence is not associated with a table.
• Same sequence can be used in multiple tables.
• It can be used in insert statement to insert identity values, it can also be used in T-Sql Scripts.

[ALSO READ] Sequence Vs Identity

Now let us understand the sequence concept using below extensive list of examples:

Create a Sequence with Initial value 1 and Increment by 1

CREATE SEQUENCE [DBO].[SequenceExample] AS INT
 START WITH 1
 INCREMENT BY 1
GO

Using Sequence in an Insert Statement

CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))
GO
--Insert records into Employee table with Sequence object
INSERT INTO dbo.Employee VALUES
(NEXT VALUE FOR DBO.SequenceExample,'BASAVARAJ BIRADAR'), 
(NEXT VALUE FOR DBO.SequenceExample,'SHREE BIRADAR'),
(NEXT VALUE FOR DBO.SequenceExample,'PRATHAM BIRADAR')
GO
-- CHECK THE RECORDS INSERTED IN THE TABLE
SELECT * FROM dbo.Employee WITH(NOLOCK)
GO

Result:
ID Name
———– —————————–
1 BASAVARAJ BIRADAR
2 SHREE BIRADAR
3 PRATHAM BIRADAR

Associate Sequence object to a table

CREATE TABLE dbo.Customer
(ID INT DEFAULT(NEXT VALUE FOR DBO.SequenceExample),
                               Name VARCHAR(100))
GO
INSERT INTO dbo.Customer(Name)
VALUES('PINKU BIRADAR'),
('MONTY BIRADAR')
GO
-- CHECK THE RECORDS INSERTED IN THE TABLE
SELECT * FROM dbo.Customer WITH(NOLOCK)
GO

Result:
ID Name
———– —————————–
4 PINKU BIRADAR
5 MONTY BIRADAR

[ALSO READ]: SEQUENCE Limitations and Restrictions

Getting Next Sequence Value in A SELECT Statement

SELECT (NEXT VALUE FOR DBO.SequenceExample) 
                            AS SequenceValue
GO 3

Note: Here GO 3 statement executes the statement above it 3 times.

Result:
Beginning execution loop
———–
6
(1 row(s) affected)

———–
7
(1 row(s) affected)

———–
8
(1 row(s) affected)
Batch execution completed 3 times.

[ALSO READ]: Sequence Cache management and Internals

Getting Sequence Next Value in a variable

DECLARE @EmpID AS INT 
        = NEXT VALUE FOR DBO.SequenceExample
SELECT @EmpID AS 'Employee Id'

Result:
Employee Id
———–
9

Re-Setting the Sequence Number

ALTER SEQUENCE DBO.SequenceExample
RESTART WITH 1 ;
GO
-- Verify whether sequence number is re-set
SELECT (NEXT VALUE FOR DBO.SequenceExample) 
                           AS SequenceValue
GO

Result:

SequenceValue
———–
1

How to get the current value of the Sequence

SELECT Current_Value 
FROM SYS.Sequences 
WHERE name='SequenceExample'
GO

Result:
Current_Value
———————————
1

Create Sequence Syntax:

CREATE SEQUENCE [schema_name . ] sequence_name
 [ AS [built_in_integer_type | user-defined_integer_type]]
 [ START WITH  ]
 [ INCREMENT BY  ]
 [ { MINVALUE [  ] } | { NO MINVALUE } ]
 [ { MAXVALUE [  ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [  ] } | { NO CACHE } ]
 [ ; ]

Where:
Start with:             the initial value to start with sequence.
Increment by:   the step by which the values will get incremented or decremented.
Minvalue:              the minimum value of the sequence.
Maxvalue:             the maximum value of the sequence.
Cycle / No Cycle:  to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
Cache / No Cache:  to pre-allocate the number of sequences specified by the given value.

[ALSO READ]:
SEQUENCE Limitations and Restrictions
Sequence Cache management and Internals