在删除后重置SQL Server中的自动增量

Question

我已经从SQL Server数据库的一个表中删除了一些记录。

在表中，ID看起来像这样:

99 100 101 1200 1201...

我想删除后面的记录（IDs > 1200），然后重置自动增量，使下一个自动生成的ID为102。这样我的记录将是连续的。在SQL Server中是否有方法可以实现这一点？

- jumbojs

---

请不要说“不要这样做”。当我询问如何做某事时，只得到不要的回答让我很反感。是的，重置标识符可能会导致外键问题，但只有在你不了解自己的数据库并相应地编写程序时才会出现。在定期删除后重置标识符有非常好的理由 - 审计员。审计员不喜欢看到间隙，所以填补它们，用受控制的方式来完成，并确保维护外键约束。 - user1484893

@spyder，你知道如果插入记录被回滚，不仅删除操作会导致间隙吗？使用自增值无法避免间隙，试图这么做是愚蠢的。我曾在审计机构工作过，有能力的审计员可以理解这一点。如果有正确的审计表，他们可以查看那些记录发生了什么。或者，如果出于法律原因绝不能存在间隙（有几种情况是这样），那么只有无能的开发人员会使用自增值，审计员们会感到不满。 - HLGEM

Answer 1

运行以下命令来重新生成mytable并从1开始：

DBCC CHECKIDENT (mytable, RESEED, 0)

在Books on Line（BOL，SQL帮助）中阅读相关信息。此外，请注意不要设置高于您所设置的种子记录。

- Robert Wagner

---

因为这些记录的ID可能会被愉快地重新使用，导致问题混乱。 - nalply

为了从1开始设置标识列，需要使用0：DBCC CHECKIDENT (mytable, RESEED, 0)。请注意，此操作只会重新设置标识列的起始值，并不会更改任何现有行的标识值。 - Ryan Lundy

"DBCC CHECKIDENT（table_name）"会将种子设置为表中最高的标识，这样您就不必“小心谨慎”了。 - user1027167

@user1027167，你的回答对我没有用。它一直在已经保存的最高ID上递增。在我的情况下，我必须显式地使用“RESEED，18”才能获得“19”作为下一个ID。否则，它会愉快地递增到“29”。 - Matthis Kohli

只有在标识列的最大值低于当前标识值时，才应更改种子。因此，如果标识值已经比较大（例如@MatthisKohli的情况），则必须调用显式重新设置。DBCC CHECKIDENT (table_name) - Martheen

DBCC CHECKIDENT -- 具有架构意识你可以用单引号或方括号将其括起来，两者都可以使用。DBCC CHECKIDENT('Chemical.Products', RESEED, 0) DBCC CHECKIDENT([Chemical.Products], RESEED, 0) - Asad Naeem

Answer 2

DBCC CHECKIDENT('databasename.dbo.tablename', RESEED, number)

如果number等于0，则在下一次插入时自动递增字段将包含值1。

如果number等于101，则在下一次插入时，自动递增字段将包含值102。

一些额外的信息...对您可能有用

在上面的查询中给出自动递增的number之前，您必须确保现有表的自动递增列包含小于number的值。

要从表(table1)获取列(column_name)的最大值，可以使用以下查询

 SELECT MAX(column_name) FROM table1

- Fathah Rehman P

---

你能不能不包括种子数，这样它就会使用最大值？例如 DBCC CHECKIDENT('databasename.dbo.tablename')，不要包括, RESEED, number？ - Sam

Answer 3

半智能化的：

declare @max int;  
select @max = max(key) from table;  
dbcc checkident(table,reseed,@max)

http://sqlserverplanet.com/tsql/using-dbcc-checkident-to-reseed-a-table-after-delete

- user423430

---

"DBCC CHECKIDENT (table_name)" 可以做到相同的效果（可能没有竞争条件）。 - user1027167

@user1027167 文档中提到“如果表的当前标识值小于标识列中存储的最大标识值”，但这并不涵盖在数据删除后的清理工作（重新使用ID通常是一个坏主意）。在SQL 2008上已经验证。 - user423430

最佳的系统化和自动化答案。太棒了！ - Mehdi Khademloo

Answer 4

如果你正在使用MySQL，尝试这样做：

ALTER TABLE tablename AUTO_INCREMENT = 1

- xaa

---

这是关于 MySQL 的回答。OP 正在询问有关 MSSQL 的问题。 翻译：这是MySQL的一个答案。OP正在询问MSSQL的相关问题。 - reformed

可能会，但这个答案仍然可以帮助像我这样的其他人。 - cegredev

Answer 5

我想到了解决方法。它是：

 DBCC CHECKIDENT ('tablename', RESEED, newseed)

- jumbojs

---

而如何获得“newseed”呢？ 并非我不知道，只是完整的答案会解释这一点。 - iGanja

Answer 6

删除并重置数据库中的所有表。

    USE [DatabaseName]
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"       -- Disable All the constraints
    EXEC sp_MSForEachTable "DELETE FROM ?"    -- Delete All the Table data
    Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 0)' -- Reseed All the table to 0
    Exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"  -- Enable All  the constraints back

-- You may ignore the errors that shows the table without Auto increment field.

- BMG

Answer 7

基于已接受的答案，对于那些遇到类似问题且需要完整架构限定的人：

([MyDataBase].[MySchemaName].[MyTable])...会导致错误，你需要在该数据库的上下文中。

也就是说，以下代码会报错：

DBCC CHECKIDENT ([MyDataBase].[MySchemaName].[MyTable], RESEED, 0)

请使用单引号将完全限定的表名括起来：

DBCC CHECKIDENT ('[MyDataBase].[MySchemaName].[MyTable]', RESEED, 0)

- tinonetic

Answer 8

有多个答案建议使用类似这样的语句:

DBCC CHECKIDENT (mytable, RESEED, 0)

但是原帖中提到“删除了一些记录”，这可能并不是所有的记录，因此值为0并不总是正确的。另一个答案建议自动查找最大当前值并将其重新设定为种子值，但如果表格中没有记录，则会出现问题，因此max()函数将返回NULL。有人评论建议简单地使用

DBCC CHECKIDENT (mytable)

为了重置这个值，但是另一个评论正确地指出，这只会将值增加到表中已经存在的最大值；如果值已经高于表中的最大值，则不会减少该值，这是OP想要做的事情。

更好的解决方案结合了这些想法。第一个CHECKIDENT将值重置为0，第二个重置为表中当前最高的值，以防表中有记录。

DBCC CHECKIDENT (mytable, RESEED, 0)
DBCC CHECKIDENT (mytable)

正如多个评论所指出的那样，请确保其他表中没有指向被删除记录的外键。否则这些外键将指向您在重新设置表后创建的记录，这几乎肯定不是您想要的。

- Michael Rodby

---

第二个命令需要是 DBCC CHECKIDENT (mytable, RESEED)。 - statler

@statler 根据 https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15 的说明，命令 "DBCC CHECKIDENT (table_name)" 和 "DBCC CHECKIDENT (table_name, RESEED)" 执行的是相同的操作。请参考备注部分的第二个条目。 - Michael Rodby

很明显，但不幸的是，如果在数据库上运行命令而没有使用RESEED，它将无法工作。文档无法解释，只能这样。- 至少在我使用的数据库上是这样。 - statler

Answer 9

我想补充这个答案，因为使用DBCC CHECKIDENT方法会在表使用模式时产生问题。请使用以下方法以确保：

DECLARE @Table AS NVARCHAR(500) = 'myschema.mytable';
DBCC CHECKIDENT (@Table, RESEED, 0);

如果您想检查操作的成功情况，请使用以下方法：

SELECT IDENT_CURRENT(@Table);

在上面的示例中，应该输出0。

- Alexander Schmidt

Answer 10

将数据库中每个键重置为自增，从上一个最高键的最大值开始：

Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 0)'

Exec sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED)'

- statler

---

这是一种巧妙的方式，可以在所有表上调用DBCC命令。不幸的是，如果没有指定重新生成值，CHECKINDENT命令对我来说只能识别最大值。@user423430仍然是唯一一个为我恢复丢失的标识值的答案。如果我能执行整个命令集就太好了。我已经尝试过了，但存储过程似乎无法接受多个命令。 - iGanja