A sequence is a user-defined object which generates a sequence of numbers, based on the specified start value and end value using increment etc., The difference between sequence and identity columns is, Identity column is bound to one particular table, whereas the sequence can be called or included in any table in the database.
Syntax
Create Sequence sequence_name as datatype start with <starting_number>
increment by <increment_value> minvalue <number> maxvalue <number> <[cycle | no cycle]
CREATE Sequence MySequence START WITH 1 increment BY 1 MinValue 1 MaxValue 5
To Read values from Sequence (Next Value)
SELECT NEXT VALUE FOR MySequence -- Initially it starts displaying 1 because in the start with clause we mentioned starting number as 1, till 5 it generates values, If you execute select statement even after 5 also
it generates the below error
Msg 11728, Level 16, State 1, Line 1
The sequence object 'MySequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
We can restart the sequence after reaching its maximum value as below.
1) we can include Cycle keyword in the create sequence command or we can Alter the existing sequence
Alter sequence mysequence cycle -- It generates values from start with to maximum, once it reaches maximum it starts from start with value again, it wont end.
Alter sequence mysequence restart -- It just restart the sequence for one time again if you query after it reaches the maximum number it throws error.
Sequence can be Include in the Insert command on a single table or multiple tables
CREATE TABLE Items (id int, NAME varchar(10))
CREATE sequence Item_Sequence AS Int START WITH 1 MINVALUE 1 MAXVALUE 1200 NO CYCLE
INSERT INTO Items VALUES (NEXT VALUE FOR Item_sequence, 'Kalyan')
GO 10
SELECT * FROM Items
SELECT Name, current_value FROM sys.sequences -- To Verify the current value of the sequence
The behaviour of the sequence is same as Identity if we include that in begin tran and commit tran
We can't rollback sequence even if the batch gets rollback.
Syntax
Create Sequence sequence_name as datatype start with <starting_number>
increment by <increment_value> minvalue <number> maxvalue <number> <[cycle | no cycle]
CREATE Sequence MySequence START WITH 1 increment BY 1 MinValue 1 MaxValue 5
To Read values from Sequence (Next Value)
SELECT NEXT VALUE FOR MySequence -- Initially it starts displaying 1 because in the start with clause we mentioned starting number as 1, till 5 it generates values, If you execute select statement even after 5 also
it generates the below error
Msg 11728, Level 16, State 1, Line 1
The sequence object 'MySequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
We can restart the sequence after reaching its maximum value as below.
1) we can include Cycle keyword in the create sequence command or we can Alter the existing sequence
Alter sequence mysequence cycle -- It generates values from start with to maximum, once it reaches maximum it starts from start with value again, it wont end.
Alter sequence mysequence restart -- It just restart the sequence for one time again if you query after it reaches the maximum number it throws error.
Sequence can be Include in the Insert command on a single table or multiple tables
CREATE TABLE Items (id int, NAME varchar(10))
CREATE sequence Item_Sequence AS Int START WITH 1 MINVALUE 1 MAXVALUE 1200 NO CYCLE
INSERT INTO Items VALUES (NEXT VALUE FOR Item_sequence, 'Kalyan')
GO 10
SELECT * FROM Items
SELECT Name, current_value FROM sys.sequences -- To Verify the current value of the sequence
The behaviour of the sequence is same as Identity if we include that in begin tran and commit tran
We can't rollback sequence even if the batch gets rollback.
No comments: