Learning SEQUENCE in SQL Server

Introduction

SQL Server's SEQUENCE object is a powerful tool for generating unique numeric values in a specific order. It was introduced in SQL Server 2012 to provide functionality similar to IDENTITY columns but with added flexibility. SEQUENCE is independent of tables, allowing developers to reuse it across multiple tables or scenarios. This article will explain the basics of SEQUENCE, compare it with other options like IDENTITY, and demonstrate its usage with a new practical example.

What is SEQUENCE in SQL Server?

A SEQUENCE is a user-defined schema-bound object that generates a sequence of numeric values. Unlike IDENTITY, which is tied to a specific table column, SEQUENCE exists independently and can be used across multiple tables or queries.

Key Features of SEQUENCE

• Independent Object: Not tied to a single table or column.
• Customizable: Allows control over the starting value, increment step, minimum, maximum, and cycle behavior.
• Reusable: Can be used in multiple tables or even in calculations.
• Flexible Usage: Values can be retrieved using the NEXT VALUE FOR function.

Syntax

CREATE SEQUENCE schema_name.sequence_name  
    AS data_type  
    START WITH <initial_value>  
    INCREMENT BY <step>  
    [MINVALUE <min_value>]  
    [MAXVALUE <max_value>]  
    [CYCLE | NO CYCLE]  
    [CACHE <cache_size> | NO CACHE]  

-- START WITH: Specifies the starting value.
-- INCREMENT BY: Specifies the increment between each value (positive or negative).
-- MINVALUE/MAXVALUE: Specifies the minimum and maximum allowed values.
-- CYCLE: Restarts the sequence when it reaches the maximum or minimum value.
-- CACHE: Improves performance by caching sequence values in memory.

Example

We will create a use case for managing order numbers in an e-commerce system. The goal is to assign unique order IDs to transactions using a SEQUENCE object.

Step 1. We will create a SEQUENCE object named order_sequence to generate unique order IDs starting from 1000 and incrementing by 10.

USE AdventureWorks2022
GO

CREATE SEQUENCE dbo.order_sequence  
	AS INT  
	START WITH 1000  
	INCREMENT BY 10

Step 2. Next, we will create a table customer_orders to store customer order details. The order_id column will use the SEQUENCE object to generate unique IDs automatically.

USE AdventureWorks2022
GO

CREATE TABLE dbo.customer_orders (
    order_id INT NOT NULL DEFAULT (NEXT VALUE FOR order_sequence),  
    customer_name VARCHAR(100) NOT NULL,  
    product_name VARCHAR(100) NOT NULL,  
    order_date DATE DEFAULT GETDATE()  
)

Step 3. Insert a few sample records into the customer_orders table. The order_id column will automatically get its value from the SEQUENCE object.

USE AdventureWorks2022  
GO  

INSERT INTO dbo.customer_orders (customer_name, product_name)  
VALUES  
    ('Naveen', 'Smartphone'),  
    ('Karthik', 'Laptop'),  
    ('Suresh', 'Tablet');

Step 4. Retrieve the data to see the order_id values generated by the SEQUENCE.

USE AdventureWorks2022
GO

SELECT * FROM dbo.customer_orders

Output

Step 5. Use the sys.sequences catalog view to check the properties of the SEQUENCE object. This query will provide details such as the current value, increment, and start value of the SEQUENCE.

SELECT * FROM sys.sequences WHERE name = 'order_sequence';

Output

Step 6. When more records are inserted, the SEQUENCE continues generating unique values.

USE AdventureWorks2022
GO

INSERT INTO customer_orders (customer_name, product_name)  
VALUES ('Kumar', 'Headphones');

Output

Advantages

• Greater Control: SEQUENCE provides more control compared to IDENTITY, such as restarting, cycling, and specifying custom increments.
• Reusability: It can be used across multiple tables or in ad hoc queries.
• Predictability: Developers can predict the next value without inserting a record (unlike IDENTITY).
• Performance: Caching values improve performance for high-volume applications.

SEQUENCE vs IDENTITY
 

Feature	SEQUENCE	IDENTITY
Scope	Independent object	Table-specific
Reusability	It can be reused across tables	Limited to one table
Customization	Highly customizable	Limited options
Predictability	Values can be previewed	Values are generated only on the insert

Conclusion

The SEQUENCE object in SQL Server is a versatile tool for generating sequential numbers, offering greater flexibility and control than IDENTITY. Whether you're building a multi-table system or need precise control over numbering, SEQUENCE is a valuable addition to your SQL Server toolkit. By leveraging SEQUENCE, you can design robust, scalable, and reusable numbering systems tailored to your application’s needs.