Sequence Gaps: Why NEXT VALUE FOR Skips Numbers

A missing number doesn't prove that someone deleted a row. Sequence gaps can come from rollback, failed work, and unused cached values. A sequence allocates identifiers outside the transaction that stores your business record.

Start With Allocation Rather Than Rows

A sequence is an independent database object. NEXT VALUE FOR requests a value whether a row is eventually committed or not. The object doesn't count successful inserts.

It supplies identifiers according to its increment and limits. That distinction changes the validation question. Searching the table for every number doesn't establish whether the sequence worked correctly.

I separate an internal key from a business document number before discussing gaps. An internal key needs reliable uniqueness, usually enforced by a constraint on the table. A regulated numbering process needs additional rules.

One mechanism doesn't automatically satisfy the other. The sample below creates a sequence in a disposable database. Its values are demonstration input, not evidence from a live system.

CREATE SEQUENCE dbo.SequenceGapDemo
AS bigint START WITH 1000 INCREMENT BY 1 CACHE 20;
SELECT NEXT VALUE FOR dbo.SequenceGapDemo AS AllocatedValue;

See the Sequence Gaps Rollback Leaves Behind

A transaction can request a value and then roll back. The requested number remains consumed. SQL Server doesn't put it back for another transaction to reuse.

That prevents confusion between an abandoned allocation and later work. The behavior is deliberate. The presence of sequence gaps doesn't say whether the failure happened before or after an insert attempted to use it.

Run the block below and inspect the next value afterward. No permanent row is created by the rollback demonstration. The variable holds an allocation made inside the transaction.

Rolling back the transaction doesn't rewind the independent sequence. I use this example early to clarify NO CACHE. That setting doesn't make failed transactions return their numbers.

DECLARE @Allocated bigint;
BEGIN TRAN;
SET @Allocated = NEXT VALUE FOR dbo.SequenceGapDemo;
SELECT @Allocated AS ValueBeforeRollback;
ROLLBACK;
SELECT NEXT VALUE FOR dbo.SequenceGapDemo AS ValueAfterRollback;

Expect Sequence Gaps From a Failed Insert

A default using NEXT VALUE FOR can allocate a value before another constraint rejects the row. That number isn't returned to the sequence. Errors involving a required value or another key therefore leave gaps without successful inserts.

The table and sequence have different responsibilities. The table validates the business row. The sequence supplies the candidate identifier used during that attempt.

The next sample catches an intentional CHECK failure. The error is part of the demonstration, so keep it out of production. Afterward, inspect the sequence metadata rather than expecting a row in the table.

Don't use the absence of that row to infer that the allocation never happened. A failed statement can still consume an identifier even when its row changes are rolled back.

CREATE TABLE dbo.SequenceGapRows
(
    RowId bigint NOT NULL DEFAULT NEXT VALUE FOR dbo.SequenceGapDemo PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity > 0)
);
BEGIN TRY
    INSERT dbo.SequenceGapRows(Quantity) VALUES (-1);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT NEXT VALUE FOR dbo.SequenceGapDemo AS NextCandidate;

Trace Sequence Gaps to the Cached Range

CACHE reserves a range and reduces the need to persist sequence state for every request. Values are handed out from memory within that range. An unexpected shutdown can discard unused cached values.

Normal shutdown handling differs because the engine can preserve the next state. Don't claim that every orderly restart must lose a full cache range. Describe the failure path accurately.

Cache size is a configuration input, not a prediction of an exact jump. Concurrent requests, earlier allocations, and restart conditions affect what you observe. An example that jumps from one printed value to another doesn't establish how many rows disappeared.

There is no required row behind each allocation. The sequence is doing its job, even when the ticket counter looks untidy.

Read the Object's Current Settings

The sys.sequences view exposes current_value, increment, is_cached, and cache_size. The current value describes sequence allocation state. It isn't the maximum committed key in a consuming table.

A sequence can serve several tables, so that comparison needs particular care. Look at cycling and exhaustion settings too. Reusing a range creates another uniqueness discussion that belongs in the table's constraints.

The inspection query reads metadata without allocating another value. That is important during diagnosis. Repeated NEXT VALUE FOR calls change the very object you are checking.

Use metadata for status and deliberate requests for demonstrations. Preserve the observed state with the timestamp and consumer names. Those facts explain a gap more accurately than a table-only scan.

SELECT name, current_value, increment, is_cached, cache_size,
       is_cycling, is_exhausted
FROM sys.sequences
WHERE object_id = OBJECT_ID(N'dbo.SequenceGapDemo');

Evaluate NO CACHE for Its Actual Promise

NO CACHE removes the unused in-memory range as a gap source. It increases persistence work for value requests. It doesn't reverse allocations after rollback, failed inserts, or abandoned application requests.

Test throughput under representative concurrency before choosing it. Changing the cache setting can make a screenshot look consecutive. It leaves the transactional behavior unchanged.

The command below changes only the demonstration sequence. Record its previous setting before a real change. A busy sequence deserves a workload comparison, including logging and request latency.

Don't restart an instance to manufacture a failure test on a shared server. A documented behavior and an isolated rehearsal are enough to establish what the cache choice does and doesn't promise.

ALTER SEQUENCE dbo.SequenceGapDemo NO CACHE;
SELECT name, is_cached, cache_size, current_value
FROM sys.sequences
WHERE object_id = OBJECT_ID(N'dbo.SequenceGapDemo');

Design Gapless Numbers as Business Work

Does the business need consecutive committed documents, or an audit trail explaining every allocated number? Those are different requirements. A gapless design uses a transactional counter under serialization, assigned at the appropriate business boundary.

That counter creates contention because concurrent work must agree on one next value. Include cancellation, retries, and retention rules before calling the design complete.

For several independent numbering series, partition the counter by the actual business identity. Protect updates with suitable locks and keep the number assignment transaction short. Never replace the counter with MAX(number) plus one under normal concurrency.

Two sessions can read the same maximum. Sequence gaps are expected for identifier allocation. Gapless numbering is a separate process with a separate cost.

Inventory every consumer before changing a sequence. Defaults, procedures, and application requests can all allocate values. A reserved range requested for batch work creates another legitimate source of unused numbers.

Check those callers before diagnosing an unexpected jump. Metadata describes the object, while the consumer inventory explains how your application uses its allocations.

Related reading on this blog: Identity Jumping 1000: IDENTITY_CACHE and Find Missing Identity Values.

A sequence is not a count of committed rows, it is an allocator of candidate identifiers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.