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 numbered 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
