需要注意的是，如果所有的数据都是通过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文档(上面有链接)以获得有关限制等的其他信息。

需要注意的是，如果所有的数据都是通过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文档(上面有链接)以获得有关限制等的其他信息。

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

DBCC CHECKIDENT ('[TestTable]', RESEED)

这将检查表并重置到下一个ID。它从MS SQL 2005到现在都是可用的。

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

在可能的情况下使用TRUNCATE总是比删除所有记录更好，因为它也不使用日志空间。

如果我们需要删除和重置种子，请记住，如果表从未被填充，并且您使用DBCC CHECKIDENT('tablenem'，RESEED,0) 那么第一条记录将得到identity = 0 如MSDN文档所述

在您的情况下，只重建索引，而不用担心丢失 级数恒等式这样的情况很常见。

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

DELETE FROM [TestTable]

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

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

在开发过程中，我一直试图为大量的表完成这项工作，这非常有效。

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

因此，您首先强制将其设置为1，然后将其设置为表中当前行的最高索引。快速和简单的休息idex。