Sequences — CREATE SEQUENCE and Application-Level ID Generation

A SEQUENCE is a database object that generates a sequential series of numeric values on demand — independent of any table. Unlike IDENTITY columns which are tied to a specific table, a sequence is standalone: multiple tables can share one sequence, you can retrieve the next value without performing an insert, and sequences support cycling, caching, and negative increments. They are the right choice when you need pre-allocated IDs, multi-table key coordination, or batch ID generation.

SEQUENCE Objects #

-- ── Create a sequence ──────────────────────────────────────────────────────
CREATE SEQUENCE dbo.seq_PostId
    AS INT
    START WITH 10000      -- first value
    INCREMENT BY 1        -- step size
    MINVALUE 10000
    MAXVALUE 2147483647   -- INT max
    NO CYCLE              -- throw error when exhausted (vs CYCLE to restart)
    CACHE 50;             -- pre-allocate 50 values in memory for performance
GO

-- ── Get the next value ────────────────────────────────────────────────────
SELECT NEXT VALUE FOR dbo.seq_PostId;    -- returns 10000 first call
SELECT NEXT VALUE FOR dbo.seq_PostId;    -- returns 10001

-- ── Use sequence as a column default ─────────────────────────────────────
ALTER TABLE dbo.Posts
ADD SequenceId INT NOT NULL
    DEFAULT (NEXT VALUE FOR dbo.seq_PostId);

-- ── Shared sequence across multiple tables ────────────────────────────────
-- All related entities share a single ID space (no ID collisions):
CREATE SEQUENCE dbo.seq_ContentId AS BIGINT START WITH 1 INCREMENT BY 1;

-- Use in multiple tables:
-- CREATE TABLE dbo.Posts    (..., Id BIGINT DEFAULT NEXT VALUE FOR dbo.seq_ContentId);
-- CREATE TABLE dbo.Comments (..., Id BIGINT DEFAULT NEXT VALUE FOR dbo.seq_ContentId);
-- Now a Post might have Id=1, and a Comment Id=2 — globally unique across content types

-- ── Batch allocation — get a range of IDs at once ─────────────────────────
DECLARE @BatchStart BIGINT;
DECLARE @BatchSize  INT = 100;

EXEC sp_sequence_get_range
    @sequence_name        = N'dbo.seq_ContentId',
    @range_size           = @BatchSize,
    @range_first_value    = @BatchStart OUTPUT;

-- @BatchStart now holds the first ID; @BatchStart to @BatchStart+99 are reserved

-- ── View sequence state ───────────────────────────────────────────────────
SELECT name, current_value, start_value, increment, is_exhausted
FROM   sys.sequences
WHERE  name = 'seq_PostId';

-- ── Reset a sequence (requires ALTER SEQUENCE) ────────────────────────────
ALTER SEQUENCE dbo.seq_PostId RESTART WITH 10000;

-- ── IDENTITY vs SEQUENCE comparison ──────────────────────────────────────
-- IDENTITY:  tied to one table, cannot pre-allocate, hidden counter
-- SEQUENCE:  independent object, shareable, pre-allocable, visible/queriable

IDENTITY vs SEQUENCE Reference #

Common Mistakes #

Mistake 1 — Expecting gapless sequences with CACHE (restart causes gaps) #

❌ Wrong — sequence with CACHE 50; server restart loses cached values; 50-value gap appears in IDs.

✅ Correct — use NO CACHE for gapless sequences (accepts performance cost) or document that gaps are expected.

Mistake 2 — Not monitoring sequence exhaustion (system fails with no warning) #

❌ Wrong — INT sequence hits MAXVALUE; all inserts fail with “sequence object exhausted” error; no monitoring in place.

✅ Correct — query sys.sequences in monitoring jobs and alert when current_value > maximum_value * 0.8.