删除SQL服务器中的记录后重置身份种子

我已将记录插入到SQLServer数据库表中。该表定义了主键，并且自动递增标识种子设置为“是”。这样做主要是因为在SQLAzure中，每个表都必须定义主键和标识。

但是由于我必须从表中删除一些记录，这些表的标识种子将受到干扰，索引列（自动生成，增量为1）将受到干扰。

删除记录后，如何重置标识列，使列按升序排列？

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

1425644 次浏览
小开 
DBCC CHECKIDENT ('TestTable', RESEED, 0)GO

其中0是identity开始值

小开
最佳答案

DBCC CHECKIDENT管理命令用于重置身份计数器。命令语法为：

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

示例：

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

以前版本的AzureSQL数据库不支持它，但现在支持它。

由于所罗门·鲁茨基，命令的文档现在已修复。

小开

这是一个常见的问题，答案总是一样的：不要这样做。身份值应该被视为任意的，因此，没有“正确”的顺序。

小开

我尝试了@anil shahs答案并重置了身份。但是当插入新行时，它得到了identity = 2。所以我将语法更改为：

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

然后第一行将获得标识=1。

小开

运行此脚本以重置标识列。您将需要进行两项更改。将tableXYZ替换为您需要更新的任何表。此外，需要从临时表中删除标识列的名称。这在包含35,000行和3列的表上是瞬时的。显然，备份表并首先在测试环境中尝试此操作。 

select *into #tempFrom tableXYZ
set identity_insert tableXYZ ON
truncate table tableXYZ
alter table #temp drop column (nameOfIdentityColumn)
set identity_insert tableXYZ OFF
insert into tableXYZselect * from #temp
小开

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

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

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

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

  • 通常使用较少的锁。

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

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

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

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

所以如下：

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

变得只是：

TRUNCATE TABLE [MyTable];

请参阅TRUNCATE TABLE留档（上面链接）以获取有关限制等的更多信息。

小开

尽管大多数答案都建议从RESEED到0，虽然有些人认为这是TRUNCATED表的缺陷，但Microsoft有一个排除ID的解决方案

DBCC CHECKIDENT ('[TestTable]', RESEED)

这将检查表并重置到下一个ID。自MSSQL2005年以来一直可用。

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

小开

最好尽可能使用截断而不是删除所有记录，因为它也不使用日志空间。

如果我们需要删除并需要重置种子，请始终记住，如果表从未填充并且您使用了DBCC CHECKIDENT('tablenem',RESEED,0)那么第一条记录将获得标识=0如上所述msdn留档

在你的情况下只有重建索引，不要担心失去一系列的身份，因为这是一个常见的场景。

小开 
DBCC CHECKIDENT (<TableName>, reseed, 0)

这会将当前标识值设置为0。

插入下一个值时，标识值递增到1。

小开

虽然大多数答案都建议将RESEED转换为0，但很多时候我们只需要重新播种到下一个可用的ID

declare @max intselect @max=max([Id]) from [TestTable]if @max IS NULL   --check when max is returned as nullSET @max = 0DBCC CHECKIDENT ('[TestTable]', RESEED, @max)

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

小开

发布2个命令可以做到这一点

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

第一个将标识重置为零，下一个将其设置为下一个可用值--雅各布

小开

@陈志立

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

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

小开

使用新id重置身份列…

DECLARE @MAX INTSELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)
小开

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

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

小开

第一：身份规范只是：“否”>>保存数据库执行项目

之后：身份规范只需：“是”>>保存数据库执行项目

您的数据库ID，PK从1开始>>

小开

使用此存储过程：

IF (object_id('[dbo].[pResetIdentityField]') IS NULL)BEGINEXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');ENDGO
SET  ANSI_NULLS ONGOSET  QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[pResetIdentityField]@pSchemaName NVARCHAR(1000), @pTableName NVARCHAR(1000) ASDECLARE @max   INT;DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;
DECLARE @identityColumn   NVARCHAR(1000);
SELECT @identityColumn = c.[name]FROM sys.tables tINNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]WHERE     c.is_identity = 1AND t.name = @pTableNameAND s.[name] = @pSchemaName
IF @identityColumn IS NULLBEGINRAISERROR('One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table', 16, 1);RETURN;END;
DECLARE @sqlString   NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;
EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT
IF @max IS NULLSET @max = 0
print(@max)
DBCC CHECKIDENT (@fullTableName, RESEED, @max)go
--exec pResetIdentityField 'dbo', 'Table'

只是重新审视我的答案。我在sql server 2008 r2中遇到了一个奇怪的行为，你应该知道。

drop table test01
create table test01 (Id int identity(1,1), descr nvarchar(10))
execute pResetIdentityField 'dbo', 'test01'
insert into test01 (descr) values('Item 1')
select * from test01
delete from test01
execute pResetIdentityField 'dbo', 'test01'
insert into test01 (descr) values('Item 1')
select * from test01

第一个选择产生0, Item 1。

第二个产生1, Item 1。如果您在创建表后立即执行重置，则下一个值为0。老实说，我并不感到惊讶Microsoft无法正确处理这些内容。我发现它是因为我有一个脚本文件来填充引用表，我有时会在重新创建表后运行该文件，有时会在表已经创建时运行该文件。

小开

对于完整的DELETE行和重置IDENTITY计数，我使用这个（SQLServer 2008 R2）

USE mydb
-- ##################################################################################################################-- DANGEROUS!!!! USE WITH CARE-- ##################################################################################################################
DECLAREdb_cursor CURSOR FORSELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'AND TABLE_CATALOG = 'mydb'
DECLARE @tblname VARCHAR(50)SET @tblname = ''
OPEN db_cursorFETCH NEXT FROM db_cursor INTO @tblname
WHILE @@FETCH_STATUS = 0BEGINIF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0BEGINEXEC('DELETE FROM ' + @tblname)DBCC CHECKIDENT (@tblname, RESEED, 0)END
FETCH NEXT FROM db_cursor INTO @tblnameEND
CLOSE db_cursorDEALLOCATE db_cursorGO
小开

我使用以下脚本来执行此操作。只有一种情况会产生“错误”，即如果您已删除表中的所有行，并且IDENT_CURRENT当前设置为1，即表中只有一行开始。

DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl);
IF @maxID IS NULLIF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)ELSEDBCC CHECKIDENT ('dbo.Tbl', RESEED, 1);ELSEDBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID);
小开

重新排序到0是不太实际的，除非你正在清理整个表。

或者安东尼·雷蒙德给出的答案是完美的。首先获取身份列的最大值，然后用最大值播种。

小开

我一直在尝试在开发过程中为大量表完成此操作，这是一种魅力。

DBCC CHECKIDENT('www.newsType', RESEED, 1);DBCC CHECKIDENT('www.newsType', RESEED);

因此，您首先强制将其设置为1，然后将其设置为表中存在的行的最高索引。idex的其余部分快速简单。

小开

我刚刚成功使用了DBCC CHECKIDENT

注意事项：

  • 引用表名时不接受方括号
  • DBCC CHECKIDENT('TableName',RESEED,n)将重置回n+1
    • DBCC CHECKIDENT('tablename',RESEED,27)将从28开始
  • 如果您遇到没有设置新的起始ID的问题-请注意，您可以通过以下方式解决此问题：
    DECLARE @NewId as INTSET @NewId =  (SELECT MAX('TableName')-1  AS ID FROM TableName)DBCC CHECKIDENT('TableName',RESEED,@MaxId)
小开

似乎这里的大多数回复都假设表是空的，并且需要重置身份值。然而，我如何阅读这个问题是@xorpower现在有一个记录为1,2,3,5,6,7,12,13,14等的表…并且想要一种方法将其返回到连续列表中。（1,2,3,4,5,6,7,8,9等…）

cfBut since I have to delete some records from the table,在这里some是一个神奇的词IMHO。

AFAIK在MSSQL中没有这样的东西；最坏的情况下，您确实可以将现有记录表转储到新表中并从那里开始。我的问题也是：为什么要这样做？IDENTITY列是最好的方法吗？

不管怎样，这里提供的关心存量数据的解决方案主要是复制临时表中的所有内容，TRUNCATE现有表；重新插入表，然后再次复制所有内容。我相信这有效，但如果你有很多数据，那么这是一个相当繁重的操作。就我个人而言，我宁愿创建一个相同的表，复制新表中的数据（也许是批量的？），然后最后SWITCH将数据复制到原始表，并再次删除新创建的表。切换后你可能需要做CHECKIDENT。这样你只需要将数据从一个表移动到另一个表一次。为了节省空间，您甚至可以在批处理复制后从原始表中DELETE相关记录。

PS：是的，我知道这是一个老问题，但考虑到它的高分计数，它仍然显示在类似问题的顶部，因为没有人提到SWITCH，但它似乎值得添加。

小开

DBCC CHECKIDENT（'[TestTable]'， RESEED，0）；GO


提交答案