IDENTITY and Sequences in SQL Server

SQL Server has two built-in ways to generate surrogate keys: the IDENTITY column property (there since the beginning, bound to one table) and SEQUENCE objects (SQL Server 2012+, standalone, closer to PostgreSQL/Oracle sequences). Most tables use IDENTITY and that's fine. This guide covers how both actually behave -- including the gap behaviors that surprise people -- and the three-way trap around reading back the value you just inserted.

IDENTITY Basics

CREATE TABLE dbo.orders (
    order_id    int IDENTITY(1,1) PRIMARY KEY,   -- seed 1, increment 1
    customer_id int NOT NULL,
    amount      decimal(10,2) NOT NULL
);
 
INSERT INTO dbo.orders (customer_id, amount) VALUES (42, 99.90);

Rules that matter:

• One identity column per table, on an integer-family type (int, bigint, smallint, tinyint, decimal(p,0)).
• You don't insert into it; SQL Server assigns the value. (Exception: IDENTITY_INSERT, below.)
• IDENTITY does not imply unique or primary key. Reseed it (or use IDENTITY_INSERT) and you can get duplicates unless a PK/unique constraint enforces otherwise. The constraint is doing that work, not the identity property.
• Pick bigint for anything with real write volume. Migrating an int identity PK that hit 2,147,483,647 is a genuinely painful outage; the 4 extra bytes are not.

Reading Back the Generated Value: the Three-Way Trap

After an insert, there are four ways to get the new key, and two of them are bugs waiting to happen:

SELECT SCOPE_IDENTITY();      -- last identity in THIS scope, THIS session ✓ default choice
SELECT @@IDENTITY;            -- last identity in THIS session, ANY scope ✗ trigger trap
SELECT IDENT_CURRENT('dbo.orders');  -- last identity in the table, ANY session ✗ race condition

• SCOPE_IDENTITY() is the right default. It ignores identity values generated in other scopes -- crucially, in triggers.
• @@IDENTITY returns the last identity generated anywhere in your session. If someone later adds an audit trigger on dbo.orders that inserts into dbo.audit_log (which has its own identity), @@IDENTITY starts returning audit-log IDs. Code that worked for years breaks the day the trigger ships.
• IDENT_CURRENT('table') is per-table but cross-session: between your insert and your read, any other connection's insert changes the answer. It's for diagnostics, not application logic.
• OUTPUT INSERTED is the most robust, and the only one that works for multi-row inserts:

INSERT INTO dbo.orders (customer_id, amount)
OUTPUT INSERTED.order_id
VALUES (42, 99.90), (43, 12.50);   -- returns both new IDs

One caveat: a plain OUTPUT clause fails if the table has an INSTEAD OF trigger (use OUTPUT INTO a table variable instead). Details on trigger interactions are in our triggers guide.

Gaps Are Normal (and the Gap-of-1000)

Identity values are not guaranteed consecutive, by design. Documented reasons (as of July 2026, per Microsoft's docs):

1. Rolled-back or failed inserts consume values. The value is allocated before the transaction outcome is known and is never reused.
2. Concurrent inserts interleave. A transaction inserting multiple rows can get non-consecutive values unless it takes an exclusive table lock.
3. The identity cache. SQL Server pre-allocates identity values in memory for performance. On an unclean restart or failover, unused cached values are lost -- classically a jump of 1,000 for int columns. Nothing is wrong; the sequence just skipped.

If the gap-of-1000 bothers you (it usually shouldn't), you can disable the cache per database:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;  -- 2017+

This trades insert throughput for smaller gaps -- and it does not make values gapless; reasons 1 and 2 still apply. The honest rule: if your design requires gapless numbers (invoice numbers in some jurisdictions, check numbers), identity and sequences are both the wrong tool. Build a serialized counter table you increment inside the same transaction, and accept the write bottleneck as the price of the requirement.

IDENTITY_INSERT and Reseeding

To insert explicit values (data migrations, restoring rows):

SET IDENTITY_INSERT dbo.orders ON;
INSERT INTO dbo.orders (order_id, customer_id, amount)  -- column list now REQUIRED
VALUES (1000000, 42, 99.90);
SET IDENTITY_INSERT dbo.orders OFF;

Only one table per session can have it ON at a time, and you must list the columns explicitly. If you insert a value above the current seed, the identity counter jumps forward automatically.

To inspect or reset the counter:

DBCC CHECKIDENT ('dbo.orders', NORESEED);        -- report current value, change nothing
DBCC CHECKIDENT ('dbo.orders', RESEED, 500000);  -- set it explicitly

Reseeding below existing values is how you manufacture duplicate-key errors next Tuesday. Almost the only good reasons to reseed: after bulk-deleting test data, or repairing after a botched migration.

Sequences

A sequence is a standalone schema-bound object, not tied to any table:

CREATE SEQUENCE dbo.order_number_seq
    AS bigint
    START WITH 1
    INCREMENT BY 1
    CACHE 50;          -- pre-allocate 50 values in memory
 
SELECT NEXT VALUE FOR dbo.order_number_seq;

What sequences can do that identity can't:

• Shared across tables. One number series feeding dbo.orders and dbo.archived_orders.
• Get the value before inserting. SET @id = NEXT VALUE FOR dbo.order_number_seq; then use @id in several statements -- no read-back dance at all.
• Range reservation. sp_sequence_get_range hands your application a block of N values in one call, useful for batch loaders.
• Cycling, min/max, restart. CYCLE wraps around; ALTER SEQUENCE ... RESTART WITH n is cleaner than DBCC CHECKIDENT.

Use one as a column default to get identity-like behavior:

CREATE TABLE dbo.orders (
    order_id bigint NOT NULL
        CONSTRAINT df_orders_id DEFAULT (NEXT VALUE FOR dbo.order_number_seq)
        PRIMARY KEY,
    customer_id int NOT NULL
);

Note SCOPE_IDENTITY() returns nothing for sequence-defaulted columns -- it's identity-only. Use OUTPUT INSERTED or fetch the value first.

Sequence gaps and CACHE

Sequences have the same gap behavior as identity, tunable via CACHE n / NOCACHE. With CACHE 50, an unclean shutdown loses up to 50 values. NOCACHE persists every increment -- slower, still not gapless (rollbacks consume values here too).

NEXT VALUE FOR restrictions

NEXT VALUE FOR is banned in a long list of places (documented; the ones you'll actually hit):

• Subqueries, CTEs, and derived tables
• Views, user-defined functions, computed columns, and check constraints
• Statements using DISTINCT, UNION, EXCEPT, or INTERSECT
• Statements using TOP or OFFSET, and the WHERE clause
• Conditional expressions (CASE, COALESCE, IIF, ISNULL)
• MERGE (except indirectly via a default constraint on the target)

The pattern that trips people up: SELECT NEXT VALUE FOR seq, * FROM src ORDER BY created_at errors -- with ORDER BY you must write NEXT VALUE FOR seq OVER (ORDER BY created_at) instead.

IDENTITY or Sequence?

Common Mistakes

Exploring identity state means poking at DBCC CHECKIDENT, sys.sequences, and sys.identity_columns; Mako's AI autocomplete helps generate those probe queries as you go.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.