提问



我已将记录插入SQL Server数据库表。该表定义了主键,并且自动增量标识种子设置为是。这主要是因为在SQL Azure中,每个表都必须定义主键和标识。


但由于我必须从表中删除一些记录,这些表的标识种子将受到干扰,并且索引列(以1为增量自动生成)将受到干扰。


删除记录后如何重置标识列,以便列按数字顺序排列?


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

最佳参考


DBCC CHECKIDENT管理命令用于重置身份计数器。命令语法是:[38]


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版本中有所不同:[39]



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



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



  C.强制当前标识值为新值

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



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


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

其它参考1


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


其中0是identity起始值

其它参考2


应该注意的是,IF 所有数据都是通过DELETE从表中删除的(即没有WHERE子句),那么只要a)权限允许它,和b)没有引用该表的FK(这似乎是这里的情况),使用TRUNCATE TABLE将是首选,因为它更有效DELETE 和同时重置IDENTITY种子。以下详细信息来自TRUNCATE TABLE的MSDN页面:[40]



  与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文档(上面链接)。

其它参考3


我尝试了@anil shahs的答案,然后重置了身份。但是当插入一个新行时,它得到了identity = 2。所以我改为将语法改为:


DELETE FROM [TestTable]

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


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

其它参考4


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

其它参考5


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


DBCC CHECKIDENT ('[TestTable]', RESEED)


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


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

其它参考6


显式提供标识列的值



  1. 首先打开身份插入 - SET Identity_Insert tblPerson ON

  2. 在插入查询中指定列列表Insert into tblPerson(PersonId, Name) values(2, 'John')



之后,您将填充标识列中的空白,如果您希望SQL Server计算该值,请关闭Identity_Insert。


SET Identity_Insert tblPerson OFF


=============================


如果已删除表中的所有行,并且要重置标识列值。


使用DBCC CHECKIDENT命令。


DBCC CHECKIDENT(tblPerson, RESEED, 0)


此命令将重置PersonId标识列。

其它参考7


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

其它参考8


@jacob


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


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

其它参考9


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


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


第一个将身份重置为零,然后下一个将其设置为下一个可用值
  - 雅各布

其它参考10


截断表是首选,因为它清除记录,重置计数器并回收dis空间。


只有在外键阻止您截断的情况下才应使用Delete和CheckIdent

其它参考11


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


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

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

其它参考12


运行此脚本以重置标识列。您需要进行两项更改。将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

其它参考13


使用此存储过程:


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'

其它参考14


DBCC CHECKIDENT (<TableName>, reseed, 0)


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


插入下一个值时,标识值将增加到1。

其它参考15


对于完整的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

其它参考16


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


如果我们需要删除并需要重置种子,请始终记住,如果从未填充过表并且您使用了DBCC CHECKIDENT('tablenem',RESEED,0)
然后第一个记录将获得identity=0
如msdn文件[42]所述



  在您的情况下,只有重建索引并且不用担心会丢失
  一系列身份,因为这是一个常见的场景。


其它参考17


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


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


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