CODEWITHSUNDEEP
sql-serveridentitysql-insertautogrow
Bonn
Bonn

Reputation: 63

Prevent SQL Server IDENTITY grow on INSERT failure

I noticed that in SQL Server, when INSERT failure, the IDENTITY ID still grows. This is not a problem in MySQL

here's the sample http://sqlfiddle.com/#!3/51b7d/8

How can I prevent that? Thanks

Upvotes: 0

Views: 154

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294487

Neither MySQL nor SQL Server guarantee compact identity. Both products can, and will, leave gaps in the generated identities. Here is a MySQL SqlFiddle proving that MySQL can leave gaps.

So now that you know that your assumption is incorrect, go back to the drawing board and change your design not to assume compact identities.

Upvotes: 2

Related Questions