Introduction

Yes, this is normal behavior in databases that use identity or auto-increment style columns. A rollback undoes the row changes made by the transaction, but it does not usually rewind the underlying identity generator, so gaps appear even when the insert never becomes permanent.

Why Identity Values Are Not Gap-Free

An identity column is designed to hand out unique values cheaply and safely under concurrency. It is not designed to guarantee a perfectly continuous sequence.

If the database had to reclaim every unused identity value after a failed insert or rollback, the generator would become a heavier synchronization point. That would reduce throughput and increase contention around something that is supposed to be fast.

That is why this behavior is common:

1CREATE TABLE Demo (
2    Id INT IDENTITY(1,1) PRIMARY KEY,
3    Name NVARCHAR(50) NOT NULL
4);
5GO
6
7BEGIN TRANSACTION;
8INSERT INTO Demo (Name) VALUES ('first');
9ROLLBACK TRANSACTION;
10
11INSERT INTO Demo (Name) VALUES ('second');
12SELECT * FROM Demo;

The visible row may have Id = 2, even though no row with Id = 1 remains.

Rollback Protects Data, Not the Generator Counter

The important mental model is that the identity mechanism behaves more like a number allocator than a normal application column. Once a value is reserved, the database usually will not try to "put it back" just because the surrounding transaction failed.

The same principle applies to many sequence objects across database systems. Their job is uniqueness, not continuity.

This distinction matters because developers often assume that "transaction rollback" means every side effect is reversed. In practice, some system-level generators are intentionally outside that promise.

Why Gaps Usually Do Not Matter

For surrogate primary keys, gaps are harmless. Joins, foreign keys, indexing, and query correctness do not depend on consecutive values.

What identity values are good at:

• uniqueness
• compact indexing
• stable references

What identity values are bad at:

• human-facing numbering
• regulatory document sequences
• proving that no failed attempt ever happened

If your application uses identity values only as internal keys, gaps should not be treated as an error.

What to Do If the Business Needs Consecutive Numbers

If the real requirement is "no missing numbers," then the solution is not to fight the identity column. The solution is to use a separate numbering strategy that matches the business rule.

That could mean:

• assigning numbers only at final commit time
• keeping a dedicated table for serialized document-number allocation
• separating the internal primary key from a business-facing invoice or ticket number

These approaches cost more in complexity and sometimes in throughput, but they reflect the real requirement instead of trying to force an identity column into the wrong job.

Reseeding Is Not a Normal Fix

Some systems let you reseed the identity value:

DBCC CHECKIDENT ('Demo', RESEED, 0);

This is an administrative tool, not a rollback mechanism. If you use it carelessly on a live table, you can create duplicate-key problems or violate assumptions held by the application.

Reseeding should be treated as a maintenance action performed with full knowledge of the existing data.

Common Pitfalls

• Treating identity columns as business sequence numbers that must be gap-free.
• Assuming a transaction rollback rewinds every database mechanism, including identity generation.
• Using identity values as evidence of row count or business continuity.
• Trying to patch missing identity values manually in production.
• Choosing identity columns when the real requirement is audited, consecutive numbering.

Summary

• Identity and auto-increment values commonly advance even when a transaction rolls back.
• This behavior is normal and reflects a design optimized for uniqueness and concurrency.
• Gaps in surrogate keys usually do not matter.
• If the business truly requires consecutive numbers, use a separate numbering strategy.
• Do not reseed identity values casually just to hide normal gaps.