使用这个存储过程:

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

对所有表使用这个sp:

补播“youtable”

在删除一条记录后(在触发程序中)

ALTER PROCEDURE [dbo].[RESEED](@Tabla nvarchar(100))

as

-- ********************* CREAR ESTA FUNCIÓN PRIMERO *******************************
/*
create function  dbo.FN_EsIdentidad(@Tabla nvarchar(100), @Campo nvarchar(100))
    returns int
    as
    begin
        return columnproperty ( object_id ( @Tabla  ), @Campo , 'IsIdentity')
    end 
GO
************************************************************************************
*/


declare @CampoIdentidad nvarchar(100)

    SELECT @CampoIdentidad  = Column_Name
        --,dbo.FN_EsIdentidad(Table_Name, Column_name) as EsIdentidad,
        --table_name, column_name 
        FROM information_schema.columns
        where dbo.FN_EsIdentidad(Table_Name, Column_name) = 1 
        and Table_Name=@Tabla


declare @Sql nvarchar(max)
declare @OutPutNum int
set @Sql = 'Select MAX(' + @CampoIdentidad + ') From ' + @Tabla
--select @CampoIdentidad


set @sql = 'Select @ValorOut=max(' + @CampoIdentidad + ') From ' + @Tabla 
declare @ParamDefinition nvarchar(max)
SET @ParamDefinition = '@ValorOut int OUTPUT'

EXECUTE sp_executesql  
    @SQL 
    ,@ParamDefinition  
    ,@ValorOut = @OutPutNum OUTPUT;  

--select @OutPutNum
set @OutPutNum  = coalesce(@OutPutNum ,0)
DBCC CHECKIDENT (@Tabla, RESEED, @OutputNum)

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

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

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

虽然大多数答案都建议RESEED到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。

需要注意的是，如果所有的数据都是通过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行和重置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