Reputation: 63
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
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