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
MAXVALUE
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.
CYCLE
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.
--OUTPUT -9223372036854775808
MINVALUE
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
Decade Counter
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
DATA TYPE
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.
Cache
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.