Prevent failed Insert from claiming next Identity value

When an INSERT fails into a table with an auto incremented identity field, the identity is开发者_Python百科 still incremented, thus producing gaps in the identity sequence. Is there a way to avoid this?

The only way to do this is to reseed the table or build your own identity generator

example

CREATE TABLE test(id INT IDENTITY, bla INT)

INSERT test VALUES(1)
INSERT test VALUES('b') --fails


DBCC CHECKIDENT(test,RESEED,1) --RESEED table
INSERT test VALUES(1)
SELECT * FROM test

DROP TABLE test

On a busy table you might get inserts after that and the reseed won't be correct anymore But why do you need this? Who cares if there are gaps

Random idea from left field: Identify what's actually causing the failed inserts and validate against that, while of course realizing that due to the validations and the inserts not being atomic, you may still get failures (for things such as duplicates in unique fields).

It seems the identity gap is the symptom, the failure is the illness. Treat the illness.

Generally you want the key to have no meaning behind it other than identifying the record. That means if there are gaps in the numbering, it should not matter. If you are inserting a record into another related table, you can always use SCOPE_IDENTITY() in your stored proc/trigger, etc.