Reset identity seed after deleting records in SQL Server

First : Identity Specification Just : "No" >> Save Database Execute Project

After then : Identity Specification Just : "YES" >> Save Database Execute Project

Your Database ID, PK Start from 1 >>

For a complete DELETE rows and reset the IDENTITY count, I use this (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

Reset identity column with new id...


DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

I tried `@anil shahs` answer and it reset the identity. But when a new row was inserted it got the `identity = 2`. So instead I changed the syntax to:

DELETE FROM [TestTable]

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

Then the first row will get the identity = 1.

Use this stored procedure:

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'


Just revisiting my answer. I came across a weird behaviour in sql server 2008 r2 that you should be aware of.

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

The first select produces `0, Item 1`.

The second one produces `1, Item 1`. If you execute the reset right after the table is created the next value is 0. Honestly, I am not surprised Microsoft cannot get this stuff right. I discovered it because I have a script file that populates reference tables that I sometimes run after I re-create tables and sometimes when the tables are already created.

I use the following script to do this. There's only one scenario in which it will produce an "error", which is if you have deleted all rows from the table, and `IDENT_CURRENT` is currently set to 1, i.e. there was only one row in the table to begin with.

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)
;

`Truncate` table is preferred because it clears the records, resets the counter and reclaims the disk space.

`Delete` and `CheckIdent` should be used only where foreign keys prevent you from truncating.

Reseeding to 0 is not very practical unless you are cleaning up the table as a whole.

other wise the answer given by Anthony Raymond is perfect. Get the max of identity column first, then seed it with max.

I've been trying to get this done for a large number of tables during development, and this works as a charm.

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

So, you first force it to be set to 1, then you set it to the highest index of the rows present in the table. Quick and easy rest of the idex.

The [`DBCC CHECKIDENT`][1] management command is used to reset identity counter. The command syntax is:

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

Example:

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

It was not supported in previous versions of the Azure SQL Database but is supported now.

----------

Thanks to [Solomon Rutzky][3] the [docs][2] for the command are now fixed.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]