The SEQUENCE object in SQL Server is an effective tool for producing distinct numerical numbers in a predetermined order. It was first included in SQL Server 2012 to offer features akin to those of IDENTITY columns with more adaptability. Because SEQUENCE is table-independent, developers can utilize it in a variety of scenarios or tables. The fundamentals of SEQUENCE will be covered in this article, along with comparisons to other options like IDENTITY and a fresh real-world example to show how to use it.

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 Hostforlife
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 Hostforlife
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 Hostforlife
GO

INSERT INTO dbo.customer_orders (customer_name, product_name)
VALUES
    ('Peter', 'Smartphone'),
    ('leon', 'Laptop'),
    ('Michael', 'Tablet');

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

SELECT * FROM dbo.customer_orders

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 Hostforlife
GO

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

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

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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.