Sequence is used to generate, sequence of numeric values as per defined specification when the sequence was created. Which can start from a particular initial number called seed. Increment or decrements by a constant number value every time. We can set its limits like Min value and Maximum Value. Moreover we can create cycle for it when maximum value achieved.
1) We can create sequence starting from 1 and increment by 1 as follows.
CREATE SEQUENCE SeqCounting START WITH 1 INCREMENT BY 1 ; Go
We can access next value of sequence as below.
SELECT NEXT VALUE FOR SeqCounting Go
With Coloumn Title
SELECT NEXT VALUE FOR SeqCounting [NextSequence] Go
Storing Next Squence in a Local Variable
DECLARE @Seq int SELECT @Seq = NEXT VALUE FOR SeqCounting SELECT @Seq AS SeqCounting Go
Alter Sequence for Increment by 10
ALTER SEQUENCE SeqCounting INCREMENT BY 10 ; Go
We can Get list of all system sequence by query bellow.
SELECT * FROM sys.sequences Go SELECT * FROM sys.sequences WHERE name = 'SeqCounting' ; Go
Drop Existing Sequence
DROP SEQUENCE SeqCounting Go
Create a sequence that begins with a specific number 1024.
CREATE SEQUENCE SeqInitCounting START WITH 1024 INCREMENT BY 1 ; Go
Create a sequence upto Maximum Value 2048
CREATE SEQUENCE SeqInitCounting START WITH 1 INCREMENT BY 1 MAXVALUE 2048; Go
After 2048 value it will throw following exception.
Msg 11728, Level xx, State x, Line x The sequence object 'SeqInitCounting' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
To get rid of above exception we can create cycle of sequence, which will again start with new value after reaching maximum value.
CREATE SEQUENCE SeqInitCounting START WITH 1 INCREMENT BY 1 MAXVALUE 5 CYCLE; Go
Note: After Reaching 5 it will not begins with 1 it will return.
MINVALUE is used to set start value after reaching sequence maximum value.
CREATE SEQUENCE SeqInitCounting START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CYCLE; Go
Following example will begins sequence with 10 and decremented by 1 up to 0 and again starts sequence from 10.
CREATE SEQUENCE SeqInitCounting START WITH 10 INCREMENT BY -1 MINVALUE 0 MAXVALUE 10 CYCLE; GO
Creating a sequence with a specific data type.
If no data type is provided, the bigint data type is used as the default.
We can defined data type return type during the time of sequence created.
CREATE SEQUENCE SeqInitCounting AS tinyint START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 11 CYCLE; GO
The following data types are allowed.
tinyint - Range 0 to 255
smallint - Range -32,768 to 32,767
int - Range -2,147,483,648 to 2,147,483,647
bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
decimal and numeric with a scale of 0.
CREATE SEQUENCE with default values
CREATE SEQUENCE SeqInitCounting; Go
it starts with -9223372036854775808 and increment by 1 every next call.
We can increases performance for applications by using Cache, which minimize the number of disk IOs.
CREATE SEQUENCE SeqInitCounting AS tinyint START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 11 CYCLE CACHE; GO
When sequence created with the CACHE option, an unexpected Server shutdown may result in the loss of sequence numbers remaining in the cache.
If the cache option is enabled without specifying a cache size, the Database Engine will select a size.
We can specify cache size also.
CREATE SEQUENCE SeqInitCounting AS tinyint START WITH 0 INCREMENT BY 1 MINVALUE 0 MAXVALUE 11 CYCLE CACHE 3; GO
If a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache.
This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.