Identity Columns and Sequences in SQL Server

"Why did order numbers jump from 10,452 to 11,453 overnight?" I get that question from an application team a couple of times a year, and the answer is always in how identity works. SQL Server has two mechanisms for surrogate keys: the IDENTITY property, bound to a single column, and SEQUENCE objects (since 2012), which are independent schema objects. They behave differently in ways that matter.

Below: both, how to read current values, how to fix them when they drift, and when I choose which.

Identity columns and reading the value back

CREATE TABLE dbo.Orders (
    OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate datetime2(0) NOT NULL DEFAULT SYSDATETIME()
);

INSERT INTO dbo.Orders (CustomerID) VALUES (42);
SELECT SCOPE_IDENTITY() AS new_order_id;

There are three functions and only one is usually right:

• SCOPE_IDENTITY() – the last identity value generated in the current scope (your batch or procedure). Use this.
• @@IDENTITY – the last identity value in the current session regardless of scope. If a trigger on Orders inserts into an audit table with its own identity, @@IDENTITY returns the audit table's value. Avoid.
• IDENT_CURRENT('dbo.Orders') – the last value generated for that table by any session. Useful for reporting, useless for "what did I just insert" because another session may have inserted in between.

For multi-row inserts, the OUTPUT clause is the only reliable way to get all generated values:

INSERT INTO dbo.Orders (CustomerID)
OUTPUT inserted.OrderID, inserted.CustomerID
SELECT CustomerID FROM #NewOrders;

Why the gaps happen

Identity values are never guaranteed to be contiguous. Gaps come from rolled-back transactions, failed inserts, deletes, and, since SQL Server 2012, from the identity cache: the engine pre-allocates values (1000 for int) in memory and logs only the allocation, so an unexpected shutdown or failover loses the unused part of the cache and the next insert continues after the block.

If an application genuinely cannot tolerate the 1000-value jumps (it should be able to, but audit requirements sometimes say otherwise), SQL Server 2017 added a database scoped switch:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;

On 2012 to 2016 the equivalent is startup trace flag 272, at a small cost per insert. Neither eliminates gaps from rollbacks; nothing does.

Inspecting and reseeding

DBCC CHECKIDENT ('dbo.Orders', NORESEED);

Reports the current identity value and the current maximum in the column. If they disagree after a bulk load or a restore from a partial copy, fix it:

DBCC CHECKIDENT ('dbo.Orders', RESEED);          -- sets identity to MAX(OrderID)
DBCC CHECKIDENT ('dbo.Orders', RESEED, 500000);  -- explicit value

To insert specific values, for instance when migrating data with existing keys:

SET IDENTITY_INSERT dbo.Orders ON;
INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate) VALUES (900001, 42, '20200501');
SET IDENTITY_INSERT dbo.Orders OFF;

Only one table per session can have IDENTITY_INSERT on at a time. After the insert, the identity seed moves to the highest inserted value automatically if it is larger than the current one.

Watch the headroom on int identities. Running out is a production outage, and I keep this query in my monthly checks:

SELECT OBJECT_NAME(object_id) AS table_name, name,
       CAST(last_value AS bigint) AS last_value,
       CAST(100.0 * CAST(last_value AS bigint) / 2147483647 AS decimal(5,2)) AS pct_of_int_used
FROM sys.identity_columns
WHERE system_type_id = 56 AND last_value IS NOT NULL
ORDER BY pct_of_int_used DESC;

Sequences

A sequence is not tied to a table. Several tables can draw from it, you can get the next value before the insert, and you can fetch a range in one call.

CREATE SEQUENCE dbo.seq_DocumentNumber
    AS int
    START WITH 100000
    INCREMENT BY 1
    MINVALUE 100000
    NO MAXVALUE
    NO CYCLE
    CACHE 50;

CREATE TABLE dbo.Invoices (
    DocumentNumber int NOT NULL PRIMARY KEY DEFAULT (NEXT VALUE FOR dbo.seq_DocumentNumber),
    Amount decimal(12,2) NOT NULL
);
CREATE TABLE dbo.CreditNotes (
    DocumentNumber int NOT NULL PRIMARY KEY DEFAULT (NEXT VALUE FOR dbo.seq_DocumentNumber),
    Amount decimal(12,2) NOT NULL
);

Invoices and credit notes now share one numbering range with no collisions. Get a value ahead of time or a whole block for a batch job:

DECLARE @next int = NEXT VALUE FOR dbo.seq_DocumentNumber;

DECLARE @first sql_variant;
EXEC sp_sequence_get_range @sequence_name = N'dbo.seq_DocumentNumber', @range_size = 1000, @range_first_value = @first OUTPUT;
SELECT CAST(@first AS int) AS first_of_1000;

CACHE has the same gap behaviour as identity: the cached-but-unused values are lost on a restart. NO CACHE avoids that with more log writes. Restarting a sequence is a simple ALTER:

ALTER SEQUENCE dbo.seq_DocumentNumber RESTART WITH 200000;
SELECT name, current_value, is_exhausted, cache_size FROM sys.sequences;

Which one?

I default to IDENTITY: use it when the key belongs to one table and the value is meaningless outside the database. It is simpler, cannot be forgotten in an INSERT, and every ORM understands it.

Use a SEQUENCE when the number must be shared across tables, must be known before the row exists (pre-printed forms, document numbers sent to another system before the commit), must follow business rules like cycling, or when you need blocks of values for bulk processing. Note that a sequence default does not stop someone inserting an explicit duplicate; the primary key does.

Checking the current position

SELECT OBJECT_NAME(object_id) AS table_name, name, seed_value, increment_value, last_value FROM sys.identity_columns;
SELECT name, start_value, increment, current_value, cache_size FROM sys.sequences;

Both catalog views show the current position so you can check them after migrations or restores.

Errors

• "Msg 8115: Arithmetic overflow error converting IDENTITY to data type int." The identity ran out of int range. Short-term fix: DBCC CHECKIDENT reseed to the negative range (-2147483648) if the application tolerates negative keys; long-term fix: alter the column to bigint, which rewrites the table.
• "Msg 544: Cannot insert explicit value for identity column in table 'Orders' when IDENTITY_INSERT is set to OFF." You listed the identity column in the insert. Either drop it from the column list or set IDENTITY_INSERT on.
• "Msg 11728: The sequence object 'seq_DocumentNumber' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated." The sequence was created with MAXVALUE and NO CYCLE. ALTER SEQUENCE ... RESTART or raise the maximum.
• "Msg 11720: NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions..." You tried to use NEXT VALUE FOR inside a subquery, CASE, WHERE, or a function. It is only allowed directly in a select list, VALUES, a SET, or a column default.

And tell the business early that gaps are normal. It saves a lot of meetings.