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

但是由于我必须从表中删除一些记录， 恕我直言，这里有个神奇的词。

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

Anyway,the solutions provided here that do care about existing data are mostly about copying everything in a temp-table, TRUNCATEing the existing table; reseeding the table and then copying everything back again. I'm sure that works but if you have a lot of data then this is a pretty heavy operation. Personally I would rather go with creating an identical table, copying the data in that new table (maybe in batches?) and then finally SWITCHing the data to the original table and dropping the newly created table again. You're likely to need to do a CHECKIDENT after the SWITCH. This way you only need to move the data from one table to another once. To save space you could even DELETE the relevant records from the original table after a batch is copied.

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

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

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

我使用下面的脚本来做到这一点。只有一种情况下，它会产生一个“错误”，即如果你已经删除了表中的所有行，而IDENT_CURRENT当前设置为1，即表中只有一行开始。

DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)
;

IF @maxID IS NULL
    IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
    ELSE
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
    ;
ELSE
    DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)
;

需要注意的是，如果所有的数据都是通过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 FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

变成:

TRUNCATE TABLE [MyTable];

请参阅TRUNCATE TABLE文档(上面有链接)以获得有关限制等的其他信息。

删除一个表的所有行，检查表中是否有标识列，将被重新播种。

****警告:所有行将被删除。＊＊＊＊＊

Create PROCEDURE ClearTableData 
    @TableName varchar(100)
AS
    EXEC ('ALTER TABLE '+@TableName+'  NOCHECK CONSTRAINT ALL ') 
    BEGIN TRY  
        EXEC ('Truncate Table'+@TableName )
        PRINT @TableName +' rows have trancated '
    END TRY  
    BEGIN CATCH  
        EXEC ('Delete From '+@TableName )
        PRINT @TableName +' rows have deleted '
    END CATCH  
    
    IF (OBJECTPROPERTY(OBJECT_ID(@TableName), 'TableHasIdentity'))  = 1 
            DBCC CHECKIDENT (@TableName, RESEED, 0) 

    EXEC ('ALTER TABLE '+@TableName+'  CHECK CONSTRAINT ALL ') 

GO

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