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

17 回答

• 15

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

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

  在您的情况下，只重建索引，不要担心丢失一系列身份，因为这是一种常见的情况 .

  回复于 2024-05-03T12:54:43+08:00
• 0

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

  ---

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

  回复于 2024-05-03T12:54:43+08:00
• 0

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

  其中0是 identity 起始值

  回复于 2024-05-03T12:54:43+08:00
• 59

  对于完整的DELETE行并重置IDENTITY计数，我使用此（SQL Server 2008 R2）

  USE mydb
  
  -- ##################################################################################################################
  -- DANGEROUS!!!! USE WITH CARE
  -- ##################################################################################################################
  
  DECLARE
    db_cursor CURSOR FOR
      SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE'
         AND TABLE_CATALOG = 'mydb'
  
  DECLARE @tblname VARCHAR(50)
  SET @tblname = ''
  
  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @tblname
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0
      BEGIN
        EXEC('DELETE FROM ' + @tblname)
        DBCC CHECKIDENT (@tblname, RESEED, 0)
      END
  
    FETCH NEXT FROM db_cursor INTO @tblname
  END
  
  CLOSE db_cursor
  DEALLOCATE db_cursor
  GO
  

  回复于 2024-05-03T12:54:43+08:00
• 57

  虽然大多数答案建议重新设置为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)
  

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

  回复于 2024-05-03T12:54:43+08:00
• 2

  发出2命令可以做到这一点

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

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

  回复于 2024-05-03T12:54:43+08:00
• 3

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

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

  回复于 2024-05-03T12:54:43+08:00
• 184

  DBCC CHECKIDENT (<TableName>, reseed, 0)
  

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

  插入下一个值时，标识值将增加到1 .

  回复于 2024-05-03T12:54:43+08:00
• 4

  我试了 @anil shahs 回答它重置了身份 . 但是当插入一个新行时，它得到了 identity = 2 . 所以我改为将语法改为：

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

  然后第一行将获得identity = 1 .

  回复于 2024-05-03T12:54:43+08:00
• -2

  应该注意的是，如果所有数据都是通过 DELETE 从表中删除（即没有 WHERE 子句），那么只要a）权限允许它，并且b）没有引用该表的FK（出现在这里是这样的情况），使用 TRUNCATE TABLE 将是首选，因为它更有效 DELETE 并同时重置 IDENTITY 种子 . 以下详细信息来自TRUNCATE TABLE的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 文档（上面链接） .

  回复于 2024-05-03T12:54:43+08:00
• 69

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

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

  例：

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

  以前版本的Azure SQL数据库不支持它，但现在支持 .

  ---

  请注意， new_reseed_value 参数因SQL Server版本according to documentation而异：

  如果表中存在行，则使用new_reseed_value值插入下一行 . 在SQL Server 2008 R2及更早版本中，插入的下一行使用new_reseed_value作为当前增量值 .

  但是， I find this information misleading （实际上只是普通错误）因为观察到的行为表明至少SQL Server 2012仍然使用new_reseed_value当前的增量值逻辑 . 微软甚至与在同一页面上找到的自己的 Example C 相矛盾：

  C.将当前标识值强制为新值以下示例强制将AddressType表中AddressTypeID列中的当前标识值强制为10.因为该表具有现有行，所以插入的下一行将使用11作为value，即为列值加1定义的新当前增量值 .

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

  尽管如此，这都为新的SQL Server版本留下了不同行为的选项 . 我想在确保微软在自己的文档中清理内容之前，唯一可以确定的方法是在使用前进行实际测试 .

  回复于 2024-05-03T12:54:43+08:00
• -4

  @jacob

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

  为我工作，我只需要先从表中清除所有条目，然后在删除后在触发点中添加上述条目 . 现在每当我删除一个条目从那里 .

  回复于 2024-05-03T12:54:43+08:00
• 4

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

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

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

  回复于 2024-05-03T12:54:43+08:00
• 5

  使用此存储过程：

  IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
    BEGIN
      EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
    END
  GO
  
  SET  ANSI_NULLS ON
  GO
  SET  QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[pResetIdentityField]
    @pSchemaName NVARCHAR(1000)
  , @pTableName NVARCHAR(1000) AS
  DECLARE @max   INT;
  DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;
  
  DECLARE @identityColumn   NVARCHAR(1000);
  
  SELECT @identityColumn = c.[name]
  FROM sys.tables t
       INNER 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 = 1
        AND t.name = @pTableName
        AND s.[name] = @pSchemaName
  
  IF @identityColumn IS NULL
    BEGIN
      RAISERROR(
        '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 NULL
    SET @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.老实说，我并不感到惊讶，微软无法正确使用这些东西 . 我发现它是因为我有一个脚本文件填充了我在重新创建表时有时会运行的引用表，有时候表已经创建了表 .

  回复于 2024-05-03T12:54:43+08:00
• 889

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

  回复于 2024-05-03T12:54:43+08:00
• 1

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

  DBCC CHECKIDENT ('[TestTable]', RESEED)
  

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

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

  回复于 2024-05-03T12:54:43+08:00
• 3

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

  仅当外键阻止您截断时，才应使用 Delete 和 CheckIdent

  回复于 2024-05-03T12:54:43+08:00