Sequence in SQL Server 2012

A sequence is a user-defined object which is use to generate a sequence of numeric value based on specified value at the time of declaration. Sequence can generate ascending or descending numeric values at a specified interval and can be recycle (restart). Sequence is independent object that’s why it is not dependent on any table unlike IDENTITY. Sequence is much faster in generating number compared to IDENTITY.

--Creating a sequence

CREATE SEQUENCE [DBO].[seq_mySequence]

AS [BIGINT]

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 999

GO

--Generating sequence

SELECT NEXT VALUE FOR [DBO].[seq_mySequence] AS Seq

GO

----Alter or Reset sequence----

ALTER SEQUENCE [DBO].[seq_mySequence]

RESTART WITH 101

INCREMENT BY 1

MINVALUE 101

MAXVALUE 999

GO

--Calling sequence----

SELECT NEXT VALUE FOR [DBO].[seq_mySequence] AS Seq

GO