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

Reset identity seed after deleting records in SQL Server

提问by xorpower

I have inserted records into a SQL Server database table. 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 Server 数据库表中。该表定义了一个主键，并且自动递增标识种子设置为“是”。这样做主要是因为在 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.

身份列不用作数据库中任何地方的外键。

回答by Petr Abdulin

The DBCC CHECKIDENTmanagement command is used to reset identity counter. The command syntax is:

该DBCC CHECKIDENT管理命令用来复位身份计数器。命令语法为：

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_valueargument 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_valuevalue. In version SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value+ the current increment value.

如果表中存在行，下一行将插入new_reseed_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. Microsoft even contradicts with its own Example Cfound on same page:

但是，我发现此信息具有误导性（实际上只是完全错误），因为观察到的行为表明至少 SQL Server 2012 仍然使用new_reseed_value+ 当前增量值逻辑。微软甚至与Example C在同一页面上发现的自相矛盾：

C. Forcing the current identity value to a new value

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.

C. 将当前身份值强制为新值

下面的示例将 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. I guess the only way to be sure, until Microsoft clear up things in its own documentation, is to do actual tests before usage.

尽管如此，这一切都为较新的 SQL Server 版本的不同行为留下了一个选项。我想唯一可以确定的方法是在使用前进行实际测试，直到 Microsoft 在其自己的文档中澄清内容。

回答by anil shah

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

Where 0 is identityStart value

其中 0 是identity起始值

回答by Solomon Rutzky

It should be noted that IF allof the data is being removed from the table via the DELETE(i.e. no WHEREclause), then as long as a) permissions allow for it, and b) there are no FKs referencing the table (which appears to be the case here), using TRUNCATE TABLEwould be preferred as it does a more efficient DELETEandresets the IDENTITYseed at the same time. The following details are taken from the MSDN page for TRUNCATE TABLE:

应该注意的是，如果所有数据都通过DELETE（即无WHERE子句）从表中删除，那么只要 a) 权限允许，并且 b) 没有引用该表的 FK（这似乎是此处的情况），使用TRUNCATE TABLE将是首选，因为它更有效DELETE并同时重置IDENTITY种子。以下详细信息取自TRUNCATE TABLE的 MSDN 页面：

Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

• Less transaction log space is used.

  The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

• Fewer locks are typically used.

  When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

• Without exception, zero pages are left in the table.

  After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

与 DELETE 语句相比，TRUNCATE TABLE 具有以下优点：

• 使用较少的事务日志空间。

  DELETE 语句一次删除一行，并在事务日志中为每个删除的行记录一个条目。TRUNCATE TABLE 通过取消分配用于存储表数据的数据页来删除数据，并仅在事务日志中记录页取消分配。

• 通常使用较少的锁。

  当使用行锁执行 DELETE 语句时，表中的每一行都被锁定以进行删除。TRUNCATE TABLE 始终锁定表（包括模式 (SCH-M) 锁）和页，但不是每行。

• 无一例外，表中剩余零页。

  执行 DELETE 语句后，该表仍然可以包含空页。例如，如果没有至少一个排他 (LCK_M_X) 表锁，就无法释放堆中的空页。如果删除操作不使用表锁，则表（堆）将包含许多空页。对于索引，删除操作可能会留下空页，尽管这些页会被后台清理进程快速释放。

如果表包含标识列，则该列的计数器将重置为为该列定义的种子值。如果未定义种子，则使用默认值 1。要保留身份计数器，请改用 DELETE。

So the following:

所以以下内容：

DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

Becomes just:

变得只是：

TRUNCATE TABLE [MyTable];

Please see the TRUNCATE TABLEdocumentation (linked above) for additional information on restrictions, etc.

TRUNCATE TABLE有关限制等的更多信息，请参阅文档（上面链接）。

回答by Atal Kishore

Although most answers are suggesting RESEED to 0, But many a times we need to just reseed to next Id available

尽管大多数答案都建议将 RESEED 设置为 0，但很多时候我们只需要重新设置为下一个可用的 Id

declare @max int
select @max=max([Id])from [TestTable]
if @max IS NULL   //check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED,@max)

This will check the table and reset to the next ID.

这将检查表并重置为下一个 ID。

回答by Mikael Engver

I tried @anil shahsanswer and it reset the identity. But when a new row was inserted it got the identity = 2. So instead I changed the syntax to:

我尝试了@anil shahsanswer 并重置了身份。但是当插入新行时，它得到了identity = 2. 因此，我将语法更改为：

DELETE FROM [TestTable]

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

Then the first row will get the identity = 1.

然后第一行将获得身份= 1。

回答by RealSollyM

Although most answers are suggesting RESEEDto 0, and while some see this as a flaw for TRUNCATEDtables, Microsoft has a solution that excludes the ID

虽然大多数的回答所提出的建议RESEED来0，虽然一些人认为这是一个缺陷TRUNCATED表，微软有一个解决方案，排除ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

This will check the table and reset to the next ID. This has been available since MS SQL 2005 to current.

这将检查表并重置为下一个ID。从 MS SQL 2005 到现在，这已经可用。

https://msdn.microsoft.com/en-us/library/ms176057.aspx

https://msdn.microsoft.com/en-us/library/ms176057.aspx

回答by jacob

issuing 2 command can do the trick

发出 2 个命令可以解决问题

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

the first reset the identity to zero , and the next will set it to the next available value -- jacob

第一个将标识重置为零，然后将其设置为下一个可用值 - jacob

回答by epic

@jacob

@雅各布

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

Worked for me, I just had to clear all entries first from the table, then added the above in a trigger point after delete. Now whenever i delete an entry is taken from there.

对我来说有效，我只需要先从表中清除所有条目，然后在删除后将上述内容添加到触发点中。现在，每当我删除一个条目时，都会从那里获取。

回答by Dyna Dave

Truncatetable is preferred because it clears the records, resets the counter and reclaims the disk space.

Truncatetable 是首选，因为它清除记录、重置计数器并回收磁盘空间。

Deleteand CheckIdentshould be used only where foreign keys prevent you from truncating.

Delete并且CheckIdent应该仅在外键阻止您截断的情况下使用。

回答by Mukesh Pandey

Reset identity column with new id...

使用新 ID 重置标识列...

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)