All replies

The identity is incremented during the transaction not just at the commit. So if you insert a row and don't commit it the identity will still be incremented. This will also happen with automatic rollbacks if you are not using explicit start
transactions and commits. For example if you try inserting a row that fails on some constraint such as a null column exception, the identity is still incremented even though the row was not inserted.

As mentioned by Olaf,SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application
should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Best regards,

Dedmon Dai

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments
or complaints to MSDN Support, feel free to contact
MSDNFSF@microsoft.com