Sequence in SQL Server 2012: Complete Guide with Examples

SQL Server 2012 introduced a new feature called SEQUENCE—a user-defined object that generates a sequence of numeric values. Unlike IDENTITY, which is tied to a specific table column, a SEQUENCE object is independent and can be shared across multiple tables or used in custom logic.

This tutorial will explain:

  • What a SEQUENCE is
  • Why and when to use it
  • How to create, use, and manage SEQUENCE objects
  • Real-world examples

What is a SEQUENCE?

A SEQUENCE is a schema-bound object that generates a sequence of numeric values, according to specified rules.

Key Features:

  • Independent of tables (unlike IDENTITY)
  • Customizable start, increment, min/max, and cycle options
  • Can be used in multiple tables or procedures

Syntax

CREATE SEQUENCE sequence_name
AS data_type
START WITH initial_value
INCREMENT BY step_value
MINVALUE min_value
MAXVALUE max_value
CYCLE|NO CYCLE
CACHE cache_size;

Required: AS, START WITH, INCREMENT BY

Optional: MINVALUE, MAXVALUE, CYCLE, CACHE

Example 1: Create and Use a Simple Sequence

Step 1: Create the Sequence

CREATE SEQUENCE dbo.InvoiceSeq
    AS INT
    START WITH 1000
    INCREMENT BY 1;

Step 2: Use the Sequence

SELECT NEXT VALUE FOR dbo.InvoiceSeq AS InvoiceNumber;

This returns: 1000, then 1001, 1002, etc., on each call.

Example 2: Use Sequence in INSERT Statement

CREATE TABLE Invoices (
    InvoiceID INT PRIMARY KEY,
    CustomerName NVARCHAR(100)
);

INSERT INTO Invoices (InvoiceID, CustomerName)
VALUES (NEXT VALUE FOR dbo.InvoiceSeq, 'Alice');

This assigns the next invoice number from the sequence.

Example 3: Sequence With Cycling

CREATE SEQUENCE dbo.LimitedSeq
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE;

Calling this 7 times returns: 1, 2, 3, 4, 5, 1, 2...

Managing SEQUENCES

View current value:

SELECT current_value FROM sys.sequences WHERE name = 'InvoiceSeq';

Alter a sequence:

ALTER SEQUENCE dbo.InvoiceSeq
RESTART WITH 5000;

Drop a sequence:

DROP SEQUENCE dbo.InvoiceSeq;

Real-World Use Cases

  1. Invoice Numbers: Generate consistent numbering across multiple invoice-related tables.
  2. Batch Processing: Assign batch IDs that reset daily or monthly.
  3. Custom Keys: When composite keys are involved, or IDENTITY doesn’t suit the need.

Notes & Best Practices

  • Use NO CACHE if you want the most accurate next number without skipped values after server restarts.
  • CACHE improves performance but may skip numbers if SQL Server crashes.
  • Be cautious with CYCLE—it can cause duplicate values if not handled properly.