Why SQL Server Identity and Sequence Numbers Have Gaps

A missing identity value is not evidence that somebody deleted a row. SQL Server can allocate a number to an insert that later rolls back, and that allocation is not undone with the row. Treating an identity column as a gap-free counter therefore creates false alarms and often leads to risky attempts to reuse identifiers.

A technical identifier answers "which row is this?" A business number may answer "which issued document occupies this position in our process?" Those requirements can have different lifecycles. Keeping them separate prevents storage mechanics from silently defining the business's numbering policy.

Observe allocation independently of commit

This example uses a temporary table and keeps the first committed row so that the sequence of allocations is clear.

CREATE TABLE #Tickets (
    TicketId int IDENTITY(1,1) PRIMARY KEY,
    Note nvarchar(80) NOT NULL
);
INSERT #Tickets(Note) VALUES (N'First committed row');
BEGIN TRANSACTION;
INSERT #Tickets(Note) VALUES (N'This row is rolled back');
ROLLBACK TRANSACTION;
INSERT #Tickets(Note) VALUES (N'Next committed row');
SELECT TicketId, Note FROM #Tickets ORDER BY TicketId;
DROP TABLE #Tickets;

The surviving identifiers are 1 and 3. Value 2 was allocated to the rolled-back insert. Nothing is missing from the final committed table: the transaction correctly removed the row, while the identity allocator continued forward.

IDENTITY is associated with one table. A SEQUENCE is a separate schema object whose values can be requested before an insert and shared across tables. That flexibility is useful when a key must be known before the row is written, but it also makes unused allocations entirely normal. Sequence values are consumed outside the transaction's rollback semantics.

Caching improves allocation efficiency and can produce additional gaps when unused cached values are lost during an unexpected shutdown. Turning caching off can reduce that particular cause, but does not reclaim rolled-back or otherwise unused values. NO CACHE is not a promise of gap-free numbering.

Also separate allocation from uniqueness enforcement. Use a primary key or unique constraint for the stored identifier. Reseeding, explicit identity inserts, or cycling sequences can create collisions if the schema does not enforce the invariant. These operations deserve controlled migration procedures rather than routine gap repair.

Retrieve the actual generated values

Do not predict the next identifier by reading MAX(Id) and adding one. Another session can insert between the read and write. Similarly, the difference between the largest identifier and row count is not a reliable count of deleted records.

For a single-row insert, SCOPE_IDENTITY returns the last identity generated in the current scope. @@IDENTITY can instead reflect an identity generated by a trigger in another scope. For multi-row writes, OUTPUT inserted.Id returns the actual generated keys, but do not assume its row order corresponds to the input order. Return or retain an explicit correlation value when mapping source records.

Rows produced by OUTPUT should not be treated as proof that the surrounding transaction ultimately committed. An error or later rollback can invalidate the write after values were observed. External notifications should follow confirmed business completion, with appropriate recovery for an uncertain commit outcome.

Identity order is also not commit order. A transaction can obtain a smaller number and commit after another transaction with a larger number. Use an explicit timestamp and a defined ordering contract for reports; use suitable change-capture mechanisms when a consumer must not miss committed updates.

Plan capacity and business numbering separately

This catalog query identifies identity columns and their last allocated values.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       name AS ColumnName,
       TYPE_NAME(user_type_id) AS DataType,
       seed_value, increment_value, last_value
FROM sys.identity_columns
ORDER BY SchemaName, TableName;

Monitor remaining range using the actual data type, seed, increment direction, and allocation rate. A positive int identity has a finite ceiling, and failed inserts still consume capacity. Avoid assuming that deleting old rows makes that ceiling farther away.

Moving from int to bigint may affect referencing foreign keys, nonclustered indexes, parameters, exports, and application types. Plan that migration before exhaustion rather than treating it as a last-minute column edit.

If the business genuinely requires a controlled document sequence, allocate that number at the appropriate issuance step, store it separately from the technical key, and define how cancellations are represented. A transactional counter can serialize allocation and become a throughput bottleneck. Its scope might be a tenant, document category, or period, but only if the business rule permits that scope.

Test concurrent issuance, cancellation, rollback, and retries around commit. The useful guarantee is an explained and enforced numbering policy, not the appearance of consecutive values in a table that was never designed to provide them.

Technical references: Microsoft Learn: IDENTITY property · Microsoft Learn: CREATE SEQUENCE · Microsoft Learn: OUTPUT clause.