🗄️ SQL Server: Fix an Identity Column Gap After a Failed Bulk Insert
An IDENTITY column’s next value is allocated the moment a row is attempted, not committed, so a bulk insert that fails partway through — or a whole transaction that gets rolled back after identity values were already handed out — leaves a permanent gap in the sequence, because SQL Server does not reclaim or roll back identity allocation along with the rest of the transaction. This is documented, by-design behavior, not a bug, but it routinely surprises anyone who assumed a rollback would also undo the numbering.
🔎 The Problem
BEGIN TRANSACTION; INSERT INTO dbo.Orders (CustomerId) VALUES (101); -- allocates Id 5001 INSERT INTO dbo.Orders (CustomerId) VALUES (102); -- allocates Id 5002 INSERT INTO dbo.Orders (CustomerId) VALUES (NULL); -- constraint violation, fails ROLLBACK; -- Next successful insert gets Id 5003, NOT 5001 again - -- the identity values 5001 and 5002 were already allocated and -- are gone permanently, even though the whole transaction rolled back
✅ Fix: Treat Gaps as Expected, Don’t Chase Contiguous Numbers
- Accept that
IDENTITYvalues were never designed to be gapless or transactional — if a business process genuinely needs a contiguous, user-visible sequence, generate that separately from the primary key, after a row has successfully committed. - For a truly gapless, human-facing number (an invoice number, say), use a dedicated counter table updated under an explicit transaction with appropriate locking, generated only once you know the related row will actually commit — not the table’s own identity column.
- If you must reuse specific identity values for a one-off data fix, use
SET IDENTITY_INSERT ONdeliberately and briefly, rather than trying to prevent gaps from occurring in the first place, which isn’t something IDENTITY is built to guarantee.
🚨 Why This Is Dangerous
- Building a finance or compliance process that assumes identity values are gapless and uses missing numbers as a signal of ‘something went wrong’, when gaps from ordinary rolled-back transactions are completely normal — leading to false alarms, or worse, false confidence after a real problem that happens not to leave a gap.
An IDENTITY column promises uniqueness, not continuity — a rollback undoes your data, never the numbering.


