SEQUENCE in SQL Server

An object connected to a user-defined schema called a sequence produces a series of numerical numbers in accordance with the specification used to construct it. A sequence can be defined as a list of integers in an ordered fashion.

An application can get the next sequence number without inserting a row by executing the NEXT VALUE FOR function, unlike identity column values that are produced when rows are entered.

Syntax:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]

sequence_name
Any integer type can be used to define a sequence. The following kinds are acceptable

built_in_integer_type | user-defined_integer_type:
Any integer type can be used to define a sequence. The following kinds are acceptable.

Also, note that any user-defined data type (alias type) that is based on one of the allowed types.
If no data type is provided, the BIGINT data type is used as the default.

START WITH <constant>
The first value returned by the sequence object. The START value must be larger than or equal to the minimum value of the sequence object and less than or equal to both the maximum and minimum values. An ascending sequence object’s lowest value and a descending sequence object’s maximum value serve as the default start values for new sequence objects.

INCREMENT BY <constant>
When calling the NEXT VALUE FOR function, this value is used to raise (or lower, if negative) the sequence object’s value. The sequence object is in ascending order unless the increment is negative, in which case it is in descending order. No increment can be zero. A new sequence object’s increment by default is 1.

[ MINVALUE <constant> | NO MINVALUE ]
Specifies the bounds for the sequence object. The default minimum value for a new sequence object is the minimum value of the data type of the sequence object. This is zero for the tinyint data type and a negative number for all other data types.

[ MAXVALUE <constant> | NO MAXVALUE
Specifies the bounds for the sequence object. The default maximum value for a new sequence object is the maximum value of the data type of the sequence object.

[ CYCLE | NO CYCLE ]
Property that determines whether the sequence object should throw an exception when its minimum or maximum value is exceeded or restart from the minimum value (or maximum for descending sequence objects). For new sequence objects, the default cycle option is NO CYCLE.

CACHE | NO CACHE
Indicate the number of sequence values that the database preallocates and stores in memory for quicker access.

EXAMPLE:
The series TestSeq is created in the following example using the SMALLINT data type, which has a range of 32,768 to 32,768. Every time a new number is created, the sequence advances by 1 starting at 1. The sequence restarts at the minimum value of 1 since it is programmed to cycle when the value rises above the maximum value of 200.

CREATE SEQUENCE TestSeq
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 200
CYCLE 
CACHE 3

Generating the Sequence:
The START WITH option of 1 is returned by the sentence below. Execute the same command a few times to observe its growth.

SELECT NEXT VALUE FOR TestSeq;

Execute the following code to confirm the cache size and see the current value.

SELECT cache_size, current_value FROM sys.sequences
WHERE name = ‘TestSeq’ ;

Please continue reading to learn how the sequence can be applied to tables.