删除SQL Server中的记录后重置标识种子

标识种子"/>

删除SQL Server中的记录后重置标识种子

本文翻译自：Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. 我已将记录插入SQL Server数据库表。 The table had a primary key defined and the auto increment identity seed is set to “Yes”. 该表定义了主键，并且自动增量标识种子设置为“是”。 This is done primarily because in SQL Azure, each table has to have a primary key and identity defined. 这主要是因为在SQL Azure中，每个表都必须定义主键和标识。

But since I have to delete some records from the table, the identity seed for those tables will be disturbed and the index column (which is auto-generated with an increment of 1) will get disturbed. 但由于我必须从表中删除一些记录，这些表的标识种子将受到干扰，并且索引列（自动生成的增量为1）将受到干扰。

How can I reset the identity column after I deleted the records so that the column has sequence in ascending numerical order? 删除记录后，如何重置标识列，以使列按数字顺序递增？

The identity column is not used as a foreign key anywhere in database. 标识列不用作数据库中任何位置的外键。

#1楼

参考：

#2楼

DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

Where 0 is identity Start value 其中0是identity起始值

#3楼

The DBCC CHECKIDENT management command is used to reset identity counter. DBCC CHECKIDENT管理命令用于重置身份计数器。 The command syntax is: 命令语法是：

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Example: 例：

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

It was not supported in a previous versions of Azure SQL Database, but is supported now. 以前版本的Azure SQL数据库不支持它，但现在支持。

Please note that new_reseed_value argument is varied across SQL Server versions according to documentation : 请注意， 根据文档 ， new_reseed_value参数在SQL Server版本中是不同的：

If rows are present in the table, the next row is inserted with the new_reseed_value value. 如果表中存在行，则使用new_reseed_value值插入下一行。 In version SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value + the current increment value. 在SQL Server 2008 R2及更早版本中，插入的下一行使用new_reseed_value +当前增量值。

However, I find this information misleading (just plain wrong actually) because observed behaviour indicates that at least SQL Server 2012 is still uses new_reseed_value + the current increment value logic. 但是， 我发现这些信息具有误导性 （实际上只是错误的），因为观察到的行为表明至少SQL Server 2012仍然使用new_reseed_value +当前的增量值逻辑。 Microsoft even contradicts with its own Example C found on same page: 微软甚至与在同一页面上找到的自己的Example C相矛盾：

C. Forcing the current identity value to a new value C.将当前标识值强制为新值

The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1. 以下示例强制将AddressType表中的AddressTypeID列中的当前标识值设置为10.因为该表具有现有行，所以插入的下一行将使用11作为值，即，为其定义的新当前增量值列值加1。

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

Still, this all leaves an option for different behaviour on newer SQL Server versions. 尽管如此，这都为新的SQL Server版本留下了不同行为的选项。 I guess the only way to be sure, until Microsoft clear up things in its own documentation, is to do actual tests before usage. 我想在确保微软在自己的文档中清理内容之前，唯一可以确定的方法是在使用前进行实际测试。

#4楼

This is a common question and the answer is always the same: don't do it. 这是一个常见的问题，答案总是一样的：不要这样做。 Identity values should be treated as arbitrary and, as such, there is no "correct" order. 身份值应被视为任意，因此，没有“正确”的顺序。

#5楼

I tried @anil shahs answer and it reset the identity. 我尝试了@anil shahs答案并重置了身份。 But when a new row was inserted it got the identity = 2 . 但是当插入新行时，它获得了identity = 2 。 So instead I changed the syntax to: 所以我改为将语法改为：

DELETE FROM [TestTable]DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO

Then the first row will get the identity = 1. 然后第一行将获得identity = 1。

#6楼

Run this script to reset the identity column. 运行此脚本以重置标识列。 You will need to make two changes. 您需要进行两项更改。 Replace tableXYZ with whatever table you need to update. 将tableXYZ替换为您需要更新的任何表。 Also, the name of the identity column needs dropped from the temp table. 此外，需要从临时表中删除标识列的名称。 This was instantaneous on a table with 35,000 rows & 3 columns. 这是在一个有35,000行和3列的桌子上的瞬间。 Obviously, backup the table and first try this in a test environment. 显然，备份表并首先在测试环境中尝试此操作。

select * 
into #temp
From tableXYZset identity_insert tableXYZ ONtruncate table tableXYZalter table #temp drop column (nameOfIdentityColumn)set identity_insert tableXYZ OFFinsert into tableXYZ
select * from #temp