SQL Server Interview Question #179

What are sequences in SQL Server?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

A sequence is a schema-scoped object that generates numeric values independently of a specific table. Unlike IDENTITY, a sequence can be shared across multiple tables and values can be requested before an INSERT.

Sequences support options such as START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, and CACHE. Like identity values, sequence-generated numbers should not be assumed to be gap-free.

Code Example

CREATE SEQUENCE dbo.OrderNumberSequence
    AS BIGINT
    START WITH 1000
    INCREMENT BY 1;
GO

SELECT NEXT VALUE FOR dbo.OrderNumberSequence;