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

What you are after will never work with identity columns.

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 URL1 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.

Surely this is not a good idea if you are inserting a lot of data – Neil Knight Apr 4 at 10:42 Sorry, I already have changed my answer :) – demas Apr 4 at 10:42.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.