Reinserting rows with identity columns

I'm implementing a queue in SQL Server (2008 R2) containing jobs that are to be performed. Upon completion, the job is moved to a history table, setting a flag to success or failure. The items in the queue table has an identity column as a primary key. The history queue has a combo of this id and a time stamp as a PK.

If a job fails, I would like the option to re-run it, and they way this is thought, is to move it back from the history table and back in to the live queue. For traceability purposes, I would like to have the reinserted row have the same ID as the original entry, which causes problems as this is an identity column.

I see two possible solutions:

1) Use IDENTITY_INSERT:

SET IDENTITY_INSERT TableName ON

-- Move from history to live queue

SET IDENTITY_INSERT TableName OFF

2) Create some custom logic to generate unique IDs, like getting the max ID value from both the live and history queue and adding one.

I don't see any real problems with 2 apart from it being messy, possibly poor performance and that it makes my neurotic skin crawl...

Option 1 I like, but I don't know the implications well enough. How will this perform? And I know that doing this to two tables at the same time will make things crash and burn. 开发者_开发百科What happens if two threads does this to the same table at the same time?

Is this at all a good way to do this for semi-commonly used stored procedures, or should this technique just be used for batch inserting data once in a blue moon?

Any thoughts on which is the best option, or is there a better way?

I'd go with Option 1 - Use IDENTITY_INSERT

SET IDENTITY_INSERT TableName ON

-- Move from history to live queue

SET IDENTITY_INSERT TableName OFF

IDENTITY_INSERT is a setting that applies to the current connection - so if another connection is doing similar, it will have no impact. The only place you get an error with using it is if you attempt to set it ON on another table without first turning it OFF on the first table.

Can't you use the original (live) identity value to insert into the history table? You say you combine it with a timestamp anyway.

Assuming that the Queue's Identity column is the one assigning "Job IDs", I would think the simplest solution would be to add a new "OriginalJobID" nullable column, potentially with FK pointing to the history table. Then when you are rerunning a job, allow it to get a new ID as it is added to the queue, but have it keep a reference to the original job in this new column.

To answer "or should this technique just be used for batch inserting data once in a blue moon", I would say yes, definitely, that's exactly what it's for.

Oops, @Damien_The_Unbeliever is right, I'd forgotten that the IDENTITY_INSERT setting is per connection. It would be complicated to get yourself into real trouble with the identity insert approach (would take something like MARS I guess, or bad error-handling). Nonetheless, I think trying to reuse IDs is a mistake!

I can see a potential performance issue when reusing identity values and that is if the identity column is indexed by a clustered index.

A strict growing number will cause inserted rows to always be added last in the clustered index and no page splits will occur.

If you start to insert reused numbers then you may cause page splits during those insertions. If that is a problem is up to your domain.