Sequence in SQL Server

In SQL Server, the sequence is a schema-bound object that generates a sequence of numbers either in ascending or descending order in a defined interval. It can be configured to restart when the numbers get exhausted.

• A Sequence is not associated with any table.
• You can refer a sequence to generate values with specific increment and interval on each execution by using NEXT VALUE FOR. You don't need to insert a row in a table (like identity column) to generate the sequence.

Use the CREATE SEQUENCE statement to create a sequence.

CREATE SEQUENCE [schema_name.] sequence_name  
    [ AS [ integer_type ] ]  
    [ START WITH start_value ]  
    [ INCREMENT BY increment_value ]  
    [ { MINVALUE [ minvalue } | { NO MINVALUE } ]  
    [ { MAXVALUE [ maxvalue ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ size ] } | { NO CACHE } ]; 

In the above syntax:

• schema_name: SCHEMA associated with the Sequence.
• sequence_name: A unique name given to the sequence in a database.
• integer_type: A sequence is defined with any of the integer types as tinyint, smallint, int, bigint, numeric, decimal, or a user - defined data type.
• start_value: The first value in the sequence.
• increment_value: This is the interval between two consecutive sequence values. If the increment value is negative, then the sequence is a decreasing sequence else it is ascending. The default increment value is 1. The Increment cannot be 0.
• minvalue | NO MINVALUE: This specifies the lower bound for a sequence. If not specified, it defaults to the minimum value of the data type of the sequence.
• maxvalue | NO MAXVALUE : specifies the upper bound for the sequence. It defaults to the maximum value of the data type of the sequence.
• CYCLE | NO CYCLE: Specifies whether the sequence object should restart from the minimum value (maximum value for descending sequence) or raise an exception when the minimum (or maximum) value is reached. NO CYCLE is the default value.
• Note: Cycling will restart the sequence from the minimum or maximum value and not from the start value.
• CACHE [ size ] | NO CACHE: Improves performance for applications using sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers.

Note: SQL Serve pre-allocates the number of sequence numbers specified by the CACHE.

Let's create a simple Sequence that starts from 5 with an increment of 2.

CREATE SEQUENCE SequenceCounter
    AS INT
    START WITH 5
    INCREMENT BY 2;

The new sequence is created under the Programmability -> Sequence folder, as shown below.

You can now execute the above sequence SequenceCounter using NEXT VALUE FOR <sequence-name>. The SequenceCounter will return 5 when you execute it for the first time.

SELECT NEXT VALUE FOR SequenceCounter AS Counter;

Now, execute the same sequence again. The counter is incremented by 2 as specified in the CREATE SEQUENCE statement.

SELECT NEXT VALUE FOR SequenceCounter AS Counter;

Every time you execute the SequenceCounter, the counter is incremented by 2.

Create a Sequence with Min, Max, Cycle

In the following example, a sequence is created with data type as Decimal(3,0). It starts with 10 and every time the sequence is executed, it is incremented by 5. The maximum value is 500. It stops after reaching 500 and since CYCLE is specified, the counter restarts from 10 again.

CREATE SEQUENCE dbo.MyDecSequence  
    AS decimal (3,0)   
    START WITH 10  
    INCREMENT BY 5  
    MINVALUE 10  
    MAXVALUE 500  
    CYCLE  
    CACHE 5 ;

Use SEQUENCE with a Table

You can use a sequence with the table while inserting or updating records. For example, consider the following table.

CREATE TABLE Training(
    TrainingId int PRIMARY KEY,
    TrainingName nvarchar(50) NOT NULL,
    TrainingDate date NOT NULL)
);

Now, use the SequenceCounter sequence in the INSERT statement to insert TrainingId values, as shown below.

INSERT INTO Training(TrainingId, TrainingName, TrainingDate)
VALUES(NEXT VALUE FOR SequenceCounter, 'SEO' , '11/23/2022');

INSERT INTO Training(TrainingId, TrainingName, TrainingDate)
VALUES(NEXT VALUE FOR SequenceCounter, 'SQL Server', '11/24/2022');

Now, let's check the inserted values using the select query.

SELECT * FROM dbo.Training

You can see that the TrainingId column of the Training table is populated with numbers generated by using the sequence.

Though, the IDENTITY column also generates sequence numbers, there are a few instances where Sequence is used instead of identity column:

• You want to share the sequence number across multiple tables.
• You need to generate a sequence number in the application before inserting it into a table.
• You need the counter to restart after a certain number is reached.
• To get several sequence numbers at the same time. You can use the stored procedure sp_sequence_get_range to retrieve several numbers at the same time.