重新插入带有标识列的行
我正在 SQL Server (2008 R2) 中实现一个队列，其中包含要执行的作业。完成后，作业将移至历史表，并设置成功或失败的标志。队列表中的项目有一个标识列作为主键。历史队列具有此 id 和时间戳的组合作为 PK。
如果作业失败，我希望可以选择重新运行它，他们的想法是将其从历史表移回实时队列。出于可追溯性的目的，我希望重新插入的行具有与原始条目相同的 ID，这会导致问题，因为这是一个标识列。
我看到两种可能的解决方案：
1）使用 IDENTITY_INSERT：
SET IDENTITY_INSERT TableName ON
-- Move from history to live queue
SET IDENTITY_INSERT TableName OFF
2）创建一些自定义逻辑来生成唯一 ID，例如从实时队列和历史队列中获取最大 ID 值并添加一个。
我没有看到 2 存在任何真正的问题，除了它很混乱，可能性能很差，而且它让我神经质的皮肤起鸡皮疙瘩……
我喜欢选项 1，但我不太了解其含义。这将如何表现？我知道同时对两个表执行此操作会使事情崩溃。如果两个线程同时对同一个表执行此操作会发生什么？
对于半常用的存储过程来说，这到底是一个好方法，还是应该将此技术用于千载难逢的批量插入数据？
有什么想法是最好的选择，或者有更好的方法吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我会选择选项 1 - 使用
IDENTITY_INSERTIDENTITY_INSERT是适用于当前连接的设置 - 因此，如果另一个连接执行类似操作，不会有任何影响。使用它时出现错误的唯一地方是，如果您尝试在另一个表上将其设置为“ON”，而没有先在第一个表上将其“OFF”，则尝试将其设置为“ON”。I'd go with Option 1 - Use
IDENTITY_INSERTIDENTITY_INSERTis 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 itONon another table without first turning itOFFon 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.
假设队列的 Identity 列是分配“作业 ID”的列，我认为最简单的解决方案是添加一个新的“OriginalJobID”可为空列，可能 FK 指向历史表。然后，当您重新运行作业时，允许它在添加到队列时获取新 ID，但让它在这个新列中保留对原始作业的引用。
要回答“或者该技术是否应该仅用于千载难逢的批量插入数据”，我会说是的，当然，这正是它的用途。
哎呀，@Damien_The_Unknowner 是对的，我忘记了
IDENTITY_INSERT设置是针对每个连接的。使用身份插入方法让自己陷入真正的麻烦会很复杂（我猜会采取类似 MARS 的方法，或者糟糕的错误处理）。尽管如此，我认为尝试重复使用 ID 是一个错误！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_INSERTsetting 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.