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

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

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

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

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

你可以CHECKIDENT重置种子

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

例子

 DBCC CHECKIDENT ('TAble', reseed,0)

-- 示例查询

您可以用以下代码插入基本数据

向表中插入数据后，先创建一个表

步骤到步骤i显示数据和删除数据显示细节理解代码

结果代码:创建带有rest种子Id的表 我用的是车管局系统。Identity_columns表具有标识

--Create Table 
DROP TABLE IF EXISTS  ExampleTable
create table ExampleTable (Id Bigint identity(1,1), Name nvarchar(10))

--Insert to ExampleTable and Delete and Show identity 
insert into ExampleTable (Name) 
select 'Test1' as NAme union all select 'Test2' as NAme

select * from ExampleTable

| Id       | Name |
| -------- | -----|
| 1        |Test1 |
| 2        |Test2 |

delete from ExampleTable

insert into ExampleTable (Name) select 'Test3' as NAme

select * from ExampleTable

| Id       | Name |
| -------- | -----|
| 3        |Test3 |

delete from ExampleTable

首次检查数据 如果表中没有数据使用种子表

如果表中有数据，使用最大id

之后用CHECKIDENT改变种子

--Find seedTable
declare @reseed int=0

if(not exists( select top 1 * from ExampleTable))
begin

    
     SELECT 
        @reseed=cast( seed_value as int)
    FROM sys.tables tables 
        JOIN sys.identity_columns identity_columns 
    ON tables.object_id=identity_columns.object_id
    where 
        tables.name='ExampleTable' 
    and OBJECT_SCHEMA_NAME(tables.object_id, db_id())='dbo'
 
      set @reseed=@reseed -1

 end
 else
 begin
   --if Table Has Data and use Max id For  seed
    set @reseed=(select top 1 id from ExampleTable order by id desc)

 end


  DBCC CHECKIDENT ('ExampleTable', reseed,@reseed)


insert into ExampleTable
(Name)
select 'Test4' as NAme


select * from ExampleTable


| Id       | Name |
| -------- | -----|
| 1        |Test4 |
 

GO

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

其中0是标识起始值

使用这个存储过程:

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，项目1。如果在表创建后立即执行重置，则下一个值为0。老实说，我并不惊讶微软不能把这些东西做好。我发现它是因为我有一个填充引用表的脚本文件，有时在重新创建表后运行，有时在已经创建表时运行。

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