Using DBCC CHECKIDENT to Reseed a Table After Delete

I imagine you are just looking for simple syntax in order to reseed the identity column of a table you just deleted from.

Here is the quick version:

[cc lang=”sql”]
DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed)
[/cc]

And here is an extended example:

[cc lang=”sql”]
— populate a table with identity
SELECT
ID = IDENTITY(int,1,1)
,name
INTO ##reseed_example
FROM dbo.sysobjects

— delete some records
DELETE FROM ##reseed_example
WHERE ID > 5

— find the current max identity
DECLARE @max_seed int = ISNULL((SELECT MAX(ID) FROM ##reseed_example),0)

— use the current max as the seed
DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed)

— let’s test
INSERT INTO ##reseed_example
(
name
)
SELECT
‘newobject’

— done
SELECT *
FROM ##reseed_example
[/cc]

it should be noted that in order to use DBCC CHECKIDENT you need to be dbo (db_owner). This does present an issue sometimes because often the reason the user is performing a delete instead of a truncate is because they do not have dbo rights. To overcome this, you can will need to create a procedure that that uses: WITH EXECUTE AS ‘dbo’. And reseed from there.