IDENTITY & SEQUENCE

SQL Server offers two ways to generate auto-incrementing numbers: IDENTITY columns (per-table) and SEQUENCE objects (database-scoped, schema-bound). They solve overlapping but distinct problems.

IDENTITY Columns

Declare with IDENTITY(seed, increment):

CREATE TABLE hr.departments (
    department_id   INT IDENTITY(10, 10) PRIMARY KEY,   -- 10, 20, 30, ...
    department_name NVARCHAR(50) NOT NULL,
    location_id     INT NULL
);

INSERT INTO hr.departments (department_name, location_id)
VALUES ('IT', 1700), ('Finance', 1700);
-- department_id is auto-assigned 10, 20

A table can have at most one identity column. The next value increments even if the row is rolled back, so identity gaps are normal.

IDENTITY_INSERT

To insert an explicit value into an identity column, temporarily enable IDENTITY_INSERT:

SET IDENTITY_INSERT hr.departments ON;
INSERT INTO hr.departments (department_id, department_name)
VALUES (270, 'Payroll');
SET IDENTITY_INSERT hr.departments OFF;

Reseeding

DBCC CHECKIDENT ('hr.departments', RESEED, 1000);
-- next insert gets 1010 (1000 + 10)

-- Useful after TRUNCATE if you want a specific starting value
DBCC CHECKIDENT ('hr.departments', RESEED, 0);

Retrieving the Last Identity: Three Functions, Three Behaviors

Function	Scope	Description
`@@IDENTITY`	Session (any table, any scope)	Last identity assigned in this session — including by triggers
`SCOPE_IDENTITY()`	Session AND scope	Last identity assigned in this scope (current proc/batch)
`IDENT_CURRENT('table')`	Any session	Last identity assigned to that table by ANYONE

-- Always prefer SCOPE_IDENTITY() — immune to trigger interference
INSERT INTO hr.departments (department_name) VALUES ('Innovation');
SELECT SCOPE_IDENTITY() AS new_id;

⚠

@@IDENTITY is famously dangerous: if a trigger on the table inserts into another identity-bearing audit table, @@IDENTITY returns the audit table's id, not yours. Always use SCOPE_IDENTITY() in application code.

The most reliable pattern, however, is OUTPUT inserted.id:

DECLARE @new_ids TABLE (id INT);

INSERT INTO hr.departments (department_name)
OUTPUT inserted.department_id INTO @new_ids
VALUES ('Cybersecurity'), ('Procurement');

SELECT id FROM @new_ids;

This works for multi-row inserts (where SCOPE_IDENTITY() returns only the last row's value).

SEQUENCE Objects (SQL Server 2012+)

A SEQUENCE is a first-class number generator, decoupled from any table:

CREATE SEQUENCE hr.OrderNumberSeq AS BIGINT
    START WITH    100000
    INCREMENT BY  1
    MINVALUE      100000
    MAXVALUE      999999999
    NO CYCLE
    CACHE 100;            -- pre-allocate 100 ids in memory

-- Get the next value
SELECT NEXT VALUE FOR hr.OrderNumberSeq;

-- Use as a default
ALTER TABLE hr.orders
    ADD CONSTRAINT DF_orders_orderno
        DEFAULT (NEXT VALUE FOR hr.OrderNumberSeq) FOR order_number;

-- Or assign explicitly during INSERT
INSERT INTO hr.orders (order_number, customer_id, order_date)
VALUES (NEXT VALUE FOR hr.OrderNumberSeq, 1001, GETDATE());

Resetting / Restarting

ALTER SEQUENCE hr.OrderNumberSeq RESTART WITH 200000;

Using One Sequence for Multiple Tables

The classic SEQUENCE use case — generate globally unique IDs across many tables:

CREATE SEQUENCE hr.GlobalEventId AS BIGINT START WITH 1;

CREATE TABLE hr.audit_login  (event_id BIGINT DEFAULT (NEXT VALUE FOR hr.GlobalEventId), ...);
CREATE TABLE hr.audit_dml    (event_id BIGINT DEFAULT (NEXT VALUE FOR hr.GlobalEventId), ...);
CREATE TABLE hr.audit_logout (event_id BIGINT DEFAULT (NEXT VALUE FOR hr.GlobalEventId), ...);

Gaps After Restart

Both IDENTITY and SEQUENCE can produce gaps — large jumps in the generated numbers — after a SQL Server restart. This is by design: the engine pre-allocates blocks of ids in memory for performance, and any unused block is "lost" on restart.

To avoid gaps in IDENTITY columns, start SQL Server with the trace flag -T272 (or use ALTER DATABASE ... SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF in 2017+). For SEQUENCE objects, set NO CACHE (slower):

ALTER SEQUENCE hr.OrderNumberSeq NO CACHE;

-- Or per-database (SQL Server 2017+)
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;

Bear in mind: gaps from rolled-back transactions still occur, regardless of caching.

IDENTITY vs SEQUENCE — When to Use Which

Need	IDENTITY	SEQUENCE
Per-table auto PK	Yes	Yes (more verbose)
Same id space across multiple tables	No	Yes
Get the next id before inserting	No	Yes — `NEXT VALUE FOR`
Reset / change increment without recreating column	Limited	Yes — `ALTER SEQUENCE`
Multiple identities per table	No	Yes — multiple sequences
Standard SQL syntax	No	Yes

Best Practices

Use SCOPE_IDENTITY() over @@IDENTITY; better still, use OUTPUT inserted.id.
Don't rely on identity values being sequential — gaps are normal and expected.
Use SEQUENCE when the id needs to be known before insert (e.g., to populate child rows in the same transaction) or shared across tables.
Never reset IDENTITY in production unless you're sure no dependent foreign keys exist.

Summary

IDENTITY(seed, increment) is the per-table auto-increment column.
SCOPE_IDENTITY() returns the last identity in the current scope; OUTPUT inserted.* is best for multi-row inserts.
SEQUENCE is a database object — get the value with NEXT VALUE FOR and use it across tables.
Gaps after restart come from identity caching; disable with trace flag 272 or IDENTITY_CACHE = OFF.