身份列中的值重复 [英] Duplicate Values in Identity Column

问题描述

我有一个表，该表具有名为id的列，该列的类型为Identity.但是此列包含重复的值1..8，然后再包含1..10

I've got a table which has a column named id which is of type Identity. But this column contains duplicate values 1..8 and then again 1..10

这在世界上怎么可能?

推荐答案

我测试了Giogri所说的内容，如果在表具有行之后启用了身份规范(至少在2008年，可能还有其他版本)，则数据库将从最高的整数值开始编号.如果您有一行，其列值为100，则启用身份"，下一个插入将为101.即使身份种子"指定为1，这也不是我所期望的，但是发生了什么.

I tested what Giogri says and if you enable the Identity Specification (at least on 2008, probably other versions, too) after the table has rows, the DB will start the numbering at the highest integer value. If you have one row with 100 as the column value, then enable Identity, the next insert will be 101. Even with Identity Seed specified as 1. Wasn't what I would have expected, but its what happened.

除了SET IDENTITY INSERT，还有一个重新设定种子的命令. DBCC CHECKIDENT命令，该命令会将您的标识值重置为您指定的值.

In addition to SET IDENTITY INSERT, there's also a reseed command. DBCC CHECKIDENT command which will reset your identity values back to what you specify.

鉴于启用身份规范实际上是从列中的最高整数开始的，因此有人可能使用了SET IDENTITY_INSERT或做了DBCC CHECKIDENT.

Given that enabling the Identity Specification actually starts at the highest integer in the column, someone probably either used SET IDENTITY_INSERT or did a DBCC CHECKIDENT.

Andomar所说的重新排序的最快方法是像这样删除/重新创建列

The quickest way to resequence as Andomar says is to drop/recreate the column like this

ALTER TABLE tbl
DROP COLUMN ident_column
GO
alter TABLE tbl
ADD ident_column int IDENTITY

SET IDENTITY_INSERT文档: http://msdn .microsoft.com/en-us/library/aa259221(SQL.80).aspx
DBCC CHECKIDENT文档: http://msdn.microsoft. com/en-us/library/aa258817(SQL.80).aspx

SET IDENTITY_INSERT docs: http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx
DBCC CHECKIDENT docs: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx

这篇关于身份列中的值重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！