I have never seen this before, the rows will be sequential but I have noticed that it skipped over a particular "ID".... 1 2 3 4 6 7 8... missing 5...
There are no transactions in the INSERT stored procedure so nothing to roll back We do not allow the deletion of records.
What else can be the case?
Probably a failed insert due to some other unique constraint on the table or a foreign key reference in the table and you try to insert an invalid fk value.
The insert doesn't have to be in a transaction.
The identity value increments even on a failed insert.
Igor mentions an important point about identities and transactions. From the docs:
Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.
Any way, identity counter does not restore your value (if you have executed with transaction or without it). The same behavior has oracle (sequences).
Identity is not transactional.
You may use your own primary key counter and control access to it.
Failed attempts generates an identity value even though it is not inserted into the data table. Then, this results to the lost of an identity (it's a shame I can no longer find the post where I have learned it!).