Sequences are introduced in SQL Server 2012, to generate sequential number according to the definition. Also, it is independent of any other objects. As comparison to IDENTITY, Identity property is specific to single table where as Sequence produces numbers to different objects.
Introduction
Sequences are newly introduced in SQL Server
2012. It is a database object bound to schema, which generates sequential
number according to the definition.
Unlike IDENTITY property, the SEQUENCE object will generate sequential numeric
values for different objects rather than a single table.
Sample for Sequence Creation
CREATE SEQUENCE SeqObj
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 5
NO CYCLE
NO CACHE;
SELECT NEXT VALUE FOR SeqObj -- 1
GO
SELECT NEXT VALUE FOR SeqObj --2
GO
SELECT NEXT VALUE FOR SeqObj -- 3
GO
SELECT NEXT VALUE FOR SeqObj --4
GO
SELECT NEXT VALUE FOR SeqObj -- 5
GO
When the sequnce exceeds the max value and sequence definition has 'NO CYCLE' option, then the below error occurs.
-- When the maxvalue crossed, it will throw error because of NOCYCLE option
SELECT NEXT VALUE FOR SeqObj
GO
Error message:
/*
Msg 11728, Level 16, State 1, Line 1
The sequence object 'SeqObj' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
*/
CYCLE option with MIN and MAX values
CREATE SEQUENCE SeqObj1
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 3
CYCLE
NO CACHE;
SELECT NEXT VALUE FOR SeqObj1 -- 1
GO
SELECT NEXT VALUE FOR SeqObj1 --2
GO
SELECT NEXT VALUE FOR SeqObj1 -- 3
GO
If you specify CYCLE option, then it will re-start the
cycle startin gfrom MINVALUE option value when it reaches MAXVALUE
SELECT NEXT VALUE FOR SeqObj1 -- 0
GO
CACHE / NO CACHE option
NO CACHE: We can specify this option if we don’t want the Sequence to use the CACHING.
CACHE: Whenever the
system/DB Server crashes, it will start the sequence value from CACHE value…
CREATE SEQUENCE SeqObj
AS INT
START WITH 1
INCREMENT BY 1
CACHE 50;
SELECT NEXT VALUE FOR SeqObj
GO 48
-- Restart the SQL sever instance
SELECT NEXT VALUE FOR SeqObj
GO
-- If SQL server stops abnormally it will take next sequence
as Cache value + 1
-- To stop SQL server abnormally you can kill the SQL server
Process in Task manager. DO NOT do it in production/critical severs
SELECT NEXT VALUE FOR SeqObj --51 but not the 49
GO
DROP SEQUENCE SeqObj
GO
Altering a Sequence:
ALTER SEQUENCE SeqObj
RESTART WITH 2
INCREMENT BY 1;
Note: Data type can’t be changed by alter
statement. And “START WITH” cannot be used in an ALTER SEQUENCE command.
Conclusion
Sequences are introduced in SQL Server 2012, it
is independent of a single object, generated sequential numeric values.