Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix an Identity Column Gap After a Failed Bulk Insert

- 17.11.26 - ErcanOPAK

🗄️ 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 IDENTITY values 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 ON deliberately 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.

— Database Engineer

Related posts:

Why MERGE Can Corrupt Data Under Concurrency

Why MERGE Can Corrupt Data Under Concurrency

SQL: Advanced Query Tuning with CTEs and Recursive Logic

SQL: Advanced Query Tuning with CTEs and Recursive Logic

SQL: Use COALESCE to Handle NULL Values

SQL: Use COALESCE to Handle NULL Values

Views: 5

Post navigation

C#: Fix a Record’s with Expression Not Cloning a Nested Collection
Windows 11: Fix Bluetooth Audio Stuttering Right After Waking Up

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (658)
  • Add Constraint to SQL Table to ensure email contains @ (613)
  • Average of all values in a column that are not zero in SQL (571)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (549)
  • Find numbers with more than two decimal places in SQL (477)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK
  • Git: Fix a Worktree Locking a Branch Needed Somewhere Else
  • Asp.Net Core: Fix a Background Service Leaking Scoped Dependencies
  • SQL Server: Fix a Columnstore Index Fragmenting After Bulk Deletes
  • Docker: Fix a Container Losing Logs After a Restart Policy
  • Asp.Net Core: Fix Output Caching Serving a Stale Authorized Page

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com