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.

One comment
surtep 20 Feb 2015 at 11:50 am

I don’t think you are re seeding the table, I just tried it. What you are actually doing is just resetting the counter for the next new record……existing records are not changed…….to totally re seed an identity field, you would have to delete the field & create it again with identity specification……

Featured Articles

 Site Author

  • Thanks for visiting!
css.php