DBCC CHECKIDENT (<TableName>, reseed, 0)

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

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

我刚刚成功地使用了DBCC CHECKIDENT

注意事项:

引用表名时不接受方括号 DBCC CHECKIDENT('TableName'，RESEED,n)将重置回n+1 例如，DBCC CHECKIDENT('tablename'，RESEED,27)将从28开始 如果你有问题没有设置新的开始id -注意到这一点，你可以修复这个:

    DECLARE @NewId as INT  
    SET @NewId =  (SELECT MAX('TableName')-1  AS ID FROM TableName)
    DBCC CHECKIDENT('TableName',RESEED,@MaxId)

这里的大多数回复似乎都假定表为空，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，它似乎值得添加。

我尝试了@anil shahs的答案，它重置了身份。但是当插入新的行时，它得到了恒等式= 2。所以我把语法改为:

DELETE FROM [TestTable]

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

那么第一行将得到恒等式= 1。

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

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

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

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

其中0是标识起始值