SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or "rolled back", they would be blocked by the existing transaction using the auto-number. For example, consider my psuedo code below with table A using an auto-number field for the ID column:

User 1------------begin transactioninsert into A ...insert into B ...update C ...insert into D ...commitUser 2-----------begin transactioninsert into A ...insert into B ...commit

If user 2's transaction starts a millisecond after user 1's, then their insert into table A would have to wait for user 1's entire transaction to complete just to see if the auto-number from the first insert into A was used.

This is a feature, not a bug. I would recommend using another scheme to generate auto-numbers if you need them to be tightly sequential.

If you depend on your identity values being gapless, then yes - you are doing it wrong. The whole point of a surrogate key to is to have no business meaning.

And, no, there is no way to change this behaivor (short of rolling your own autoincrement, and suffering the performance consequences of blocking other inserts).

You get gaps in your sequence if you DELETE a row too.

Sequences are required to be unique, but they are not required to be sequential. The fact that they are monotonically increasing is just a fluke of implementation.