Sequence Objects in SQL Server

A Sequence is a user-created database Object that can be shared by multiple users to generate integers. In Simple words, we can say that Sequence objects are used to generate numeric values.
The Sequence Object was introduced to SQL Server with the rollout of SQL Server 2012.

Sequence Objects are similar to the IDENTITY column in any SQL Table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence Objects are used both independently and within the DML statements i.e. INSERT, DELETE and UPDATE. Can automatically generate unique numbers. It is a sharable object. Mainly used to create Primary Key values. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.

List of Reasons why a Sequence object might be useful:

• Requirements call for more than one column in a table to be populated with a generated sequence number.
• There is a need to keep sequence numbers synchronized across multiple tables.
• Requires using a sequence number value to be stored in data type other than numeric.
• Need to have a nullable column but yet still be populated with a sequence number.
• An application requires the sequence number value to be obtained before a row is inserted into a table.
• want multiple sequence numbers to be acquired with code at the same time.

Creating a Sequence Object

The Sequence object creation syntax is as follows, and the parameters usage is also covered.

Syntax:

CREATE SEQUENCE [Sequence_name]
    AS <data type>
    START WITH <initial_value>
    INCREMENT BY <increment_value>
    MINVALUE <min_value> | NO MINVALUE
    MAXVALUE <max_value> | NO MAXVALUE
    CYCLE | NO CYCLE
    CACHE value | NO CACHE

The syntax works as follows

Parameter	Description
CREATE SEQUENCE	Used to create a sequence followed by a database schema and the name of the sequence
AS	The data type of the Sequence can be Decimal, SmallInt, TinyInt, Int, and BigInt. Where BigInt is the default value.
START WITH	Initialize the starting value for the sequence
INCREMENT BY	Sets the amount by which you want to increment you sequence object
MINVALUE	Specifies the minimum value for sequence object, it is an optional parameter.
MAXVALUE	Specifies the maximum value for the sequence object, it is an optional parameter
CYCLE	It specifies whether the sequence should be restarted once it has reached its maximum or minimum value, it is an optional parameter
CACHE	Used to cache sequence object value, it is also an optional parameter with a default value of no-cache

Examples of Sequence Objects in SQL Server

Let’s take a look at a simple example of a sequence object creating primary key values using sequence.

Query:

CREATE SEQUENCE Counter
        START WITH 201232
        INCREMENT BY 1
        MAXVALUE 999999

Output:
In the query, we created a sequence object named ‘Counter’ and the type of this sequence object is decimal(6,0) indicates a decimal number with a precision of 6 digits and no decimal places, it starts from 201232 and increments by 1.

For checking the next value that the sequence will generate without involving any specific table.

SELECT Counter.nextval FROM dual;

The concept of dual is used as a dummy table for situations where a table reference is necessary but actual table content is not relevant.

Inserting Using Sequence

Query:

INSERT INTO Employees VALUES (Counter.nextval, 'Ram', 'Delhi', 50000, TO_DATE('2023-01-15', 'YYYY-MM-DD'));

Output:
The table ‘Employees’ will now have a new row added and it will now look like this:

Altering a Sequence

To modify an existing sequence, we can use the ALTER SEQUENCE statement. Have a look.
Query:

ALTER SEQUENCE Counter RESTART WITH 201294;

Output:
The above query will modify the existing sequence object ‘Counter’ by updating its starting value to 201294.

INSERT INTO Employees VALUES (Counter.nextval, 'Vedant', 'Goa', 95000, TO_DATE('2021-07-22', 'YYYY-MM-DD'));

Decrementing a Sequence Object

To decrement sequence, set the value of INCREMENT BY to a negative number. Setting INCREMENT BY to -5 decrements the sequence object by 5.

CREATE SEQUENCE Counter_two
    AS INT
    START WITH 100
    INCREMENT BY -5

Now execute the following script twice:

SELECT Counter_two.nextval FROM dual;

Output:
You will see 95 in the output. This is because the first time you execute the command above, the value of the ‘Counter_two’ sequence object is initialized to 100. Executing the script again decrements it by 5 to 95.

Setting the MIN and MAX Value

You can set the minimum and maximum value that your sequence can reach. If you try to increment or decrement a sequence object beyond its range an exception is thrown.

CREATE SEQUENCE Counter_three
START WITH 95
INCREMENT BY 5
MAXVALUE 100

Now execute the following script twice:

SELECT Counter_three.nextval FROM dual;

Output:
We created a sequence object named ‘Counter_three’. It has a starting value as well as an increment of 5. The maximum value for this sequence object is 100.

Now if you increment the value of this sequence object beyond its range, an error will be thrown.

Error: Sequence COUNTER_THREE.NEXTVAL exceeds MAXVALUE and cannot be instantiated.

Conclusion

SQL Server 2012 offers a flexible and independent way to generate sequential numbers, providing a valuable tool for various scenarios requiring unique identifiers or ordered numeric values within the database.Sequences offer a systematic and efficient way to generate numeric values, providing a streamlined alternative to traditional methods like identity columns or GUIDs.