Prevent SQL Server IDENTITY grow on INSERT failure

Question

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

I'm not sure. I came from MySQL background, and would like to sync between SQL-Server and MySQL — Bonn, Apr 01 '13 at 05:00

Remus Rusanu · Answer 1 · 2013-04-01T08:06:43.687

2

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.

edited Apr 01 '13 at 08:06

answered Apr 01 '13 at 07:46

Remus Rusanu

288,378
40
442
569

your fiddle shows that the MySQL INSERT was SUCCESS. Being rollbacked doesn't mean the INSERT was failure. – Bonn Apr 01 '13 at 16:46

Prevent SQL Server IDENTITY grow on INSERT failure

1 Answers1