SQL Server - how to ensure identity fields increment correctly even in case of rollback

In SQL Server, if a transaction involving the inserting of a new row gets rolled back, a number is skipped in the identity field.

For example, if the highest ID in the Foos table is 99, then we try to insert a new Foo record but roll back, then ID 100 gets 'used up' and the next Foo row will be num开发者_如何学编程bered 101.

Is there any way this behaviour can be changed so that identity fields are guaranteed to be sequential?

---

What you are after will never work with identity columns.

They are designed to "give out" and forget, by-design so that they don't cause waits or deadlocks etc. The property allows IDENTITY columns to be used as a sequence within a highly transactional system with no delay or bottlenecks.

To make sure that there are no gaps means that there is NO WAY to implement a 100-insert per second system because there would be a very long queue to figure out if the 1st insert was going to be rolled back.

For the same reason, you normally do not want this behaviour, nor such a number sequence for a high volume table. However, for very infrequent, single-process tables (such as invoice number by a single process monthly), it is acceptable to put a transaction around a MAX(number)+1 or similar query, e.g.

declare @next int
update sequence_for_tbl set @next=next=next+1
.. use @next

---

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