Now that's an approximate sequence of operations Im performing:
SET IDENTITY_INSERT <table-name> ON; INSERT SOMETHING to <table-name> with explicitly specifyed id DECLARE @oldID bigint SELECT @oldID = <some simple logic here> DBCC CHECKIDENT ('<table-name>', RESEED, @oldID) WITH NO_INFOMSGS; SET IDENTITY_INSERT <table-name> OFF;
So, as you see, everything's simple: basically, I insert record with explicit ID, then I want to return the identity seed back to its previous value (because the insert will obviously shift it).
I need this kind of strange logic (from the first POW) becase I can have ID-s in a large range (billions of rows) of values where the first digit has some special meaning.
For instance, I have current identity seed 1000007777. Then I want to insert a record with ID 2000007777 (this will shift identity to 2000007778). But I want to turn it back to 1000007777, because I believe that I will never reach the point when an identity collision will occur (I just don't operate such volumes of data). As I said, the first digit serves special purposes.
Now, the problem is, if I rollback the transaction after insert, the identity will not get rolled back (as i got it, that's the design of SQL server, and I believe many DB providers).
I can handle this manually if I am the one who conciously performes the rollback. But what if there is deadlock (or some error) right at the point before reseed?
How can I handle that (except handling the exception and performing unconditional reseed)? Any ideas?