为什么我的 SQL Server 标识值被重复使用？
我有一个表，用于在将信息发送到另一个系统（Dynamics CRM）之前对信息进行排队。我将信息写入该队列，然后另一个服务出现并将数据写入 CRM。
我使用主键的身份值并将该键与信息一起保存，以便在出现任何问题时可以跟踪它的来源。将数据从队列中取出后，我删除该记录。
然而，SQL Server 似乎正在重用身份字段上的键。许多 id 只使用一次，但许多 id 被使用两次，相当多的 id 被使用了 3 次。显然，这使得通过该 id 查找历史记录变得毫无用处。我做错了什么吗？我认为标识值应该是唯一的，表不应该重用它们。
以下是该列的属性（如果有帮助的话）。
主键属性的更多属性：
I have a table that I am using to queue information before it is sent off to another system (Dynamics CRM). I write information to that queue and another service comes along and writes the data to CRM.
I used an identity value for the primary key and save this key with the information so I can track where it came from if anything goes wrong. After pulling the data out of the queue, I delete the record.
However, it looks like SQL Server is reusing keys on the identity field. Many ids are used only once, but many are used twice and quite a few have been used three times. Obviously this makes looking up a history by that id useless. Am I doing something wrong? I thought identity values should be unique and a table should not reuse them.
Here are the properties for the column, if that helps.
More properties on the primary key property:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
当需要时，标识值在表中将是唯一的。
如果您截断表，SQL 引擎将不知道旧的标识值。
如果您想要一个在表的生命周期内仅使用一次的值，您应该考虑 GUID 或基于身份 + 日期时间创建唯一键。
Identity values will be unique within the table as the moment it is needed.
The SQL engine has no knowledge of old identity values if you truncate the table.
If you want a value that will only ever be used once during the lifetime of a table you should consider a GUID or create a unique key based on identity + datetime.
不确定如何检索身份（@@identity 等），但不同的方法存在一些与范围相关的限制。
以下是关于获取身份的 3 种方法的（有限但公平的）文章：
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
Not sure how you're retrieving the identity (@@identity, etc.), but there are some scope-related limitations with the different approaches to this.
Here is a (limited, but fair) write-up on 3 methods for getting identity:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
IDENTITY 本身并不意味着唯一 - 它只是意味着从当前种子值自动递增增量值。它并不关心是否已经存在这样的值。如果您还将该列声明为唯一（在像您这样的情况下，这通常意味着将其标记为主键），您将不会获得任何这些重复项，并且您应该很快找出谁有问题，因为应用程序或用户将收到违反约束的错误。
除了 TRUNCATE TABLE 之外，还有其他方法可以使 IDENTITY 值类似违反：
DBCC CHECKIDENT() 可用于手动将当前种子值设置为用户想要的任何值。
SET IDENTITY_INSERT 可用于手动覆盖下一个可用值。 Scribe 可能会这样做，这样他们就不必关心您是否将列设置为标识值，他们仍然可以用他们想要的任何内容覆盖它。
您可以运行服务器端跟踪来捕获这些事件，以查明是否有任何事件被调用。
指定您正在使用的 SQL Server 版本总是有用的，或者至少用您需要支持的最低版本来标记问题，因为 SQL Server 2000 的解决方案和行为通常可能与 SQL 有很大不同服务器2008 R2。只是对未来问题的有用提示。
IDENTITY on its own does not mean UNIQUE - it just means auto-increment from the current seed value by the increment value. It doesn't care if there is already such a value. If you also declare the column as unique (in situations like yours, this usually means marking it as the primary key), you will not get any of these duplicates, and you should find out quite quickly who is at fault because the application or the user will get constraint violation errors.
Other than TRUNCATE TABLE, there are other ways that IDENTITY values can be similarly violated:
DBCC CHECKIDENT() can be used to manually set the current seed value to anything the user wants.
SET IDENTITY_INSERT can be used to manually override the next available value. Scribe may be doing this so that they don't have to care whether you've made a column an identity value or not, they can still override it with whatever they want.
You could run a server-side trace that would capture these events to find out if any of them are being called.
It's always useful to specify which version of SQL Server you're using, or at least tag the question with the minimum version you need to support, as the solution and behavior for SQL Server 2000, for example, can often be quite different than SQL Server 2008 R2. Just a helpful tip for future question.
TRUNCATE TABLE语句将清除标识值。还可以通过将IDENTITY_INSERT设置为ON来专门插入身份值。在不了解系统设计方式的情况下，我建议将该标识列设置为主键（如果适用）或唯一索引。查看哪些应用程序随后无法更新数据库将显示新标识列的来源和方式。
但是，如上所述，如果您使用 TRUNCATE TABLE 并只是在其他地方公开这些标识列（例如将它们保留在其他表中），那么这是预期的行为。
如果您要从表中删除所有行并希望防止身份被重新播种，则必须使用
DELETE命令。请参阅 MSDNA
TRUNCATE TABLEstatement will clear the identity values. Identity values can also be specifically inserted by settingIDENTITY_INSERTtoON.Without knowing much about how your system is designed, I would suggest making that identity column either a primary key (if appropriate) or a unique index. Seeing what applications subsequently fail to update your database will show how and where the new identity columns are coming from.
However, as stated above if you are using
TRUNCATE TABLEand simply exposing these identity columns elsewhere (preserving them in some other table for instance), then this is expected behavior.If you are deleting all rows from the table and want to keep the identity from being reseeded, you have to instead use the
DELETEcommand. See MSDN如果您有相关表，我要做的第一件事就是确保您实际上已经建立了 PK/FK 关系。许多开发人员只是假设应用程序会处理此问题，并在发生此类情况时遇到麻烦。如果表具有外键，则不能截断该表，这会阻止身份的重用。
如果同一个表中的标识同时被多次使用，则根据定义，该标识不能是 PK，除非它是多个字段 PK 的一部分。如果您确实遇到了这种情况，我建议最好的办法是咨询微软，因为已经发生了一些完全紧张的事情。如果人们把旧的扔掉并重新使用它们，那就是另一回事了。
显然，有一些进程或人出于某种未知的原因重置了过去的值。您可以使用 DDL 触发器来记录是否有人更改 PK、删除外键约束等。至少这样您可以找到哪个进程正在执行此操作。
If you have related tables, the very first thing I would do is make sure that you have actually set up the PK/FK relationships. Many developers just assume the application will handle this and get into trouble when something like this happens. You cannot truncate the table if it has foreign keys whchi would prevent the reuse of the identities.
If there are multiple uses of the identity in the same table at the same time, the identity cannot by definintion be a PK unless it is part of a mulitple field PK. If you actually have this case, I suggest the best thing to do is to consult with with Microsoft as someting entirely strinage has happened. If people are delting old ones and reusing them, that is a differnt story.
Apparently there is some process or person who has reset the values on the past for some unknown reason. You can use DDL triggers to record if someone changes the PK, drops Foreign key constraints etc. At least this way you can find what process is doing this.