Thursday, May 16, 2013

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



No comments:

Post a Comment