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:

DBCC CHECKIDENT('##reseed_example', RESEED, @max_seed)

And here is an extended example:

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

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.

Featured Articles

 Site Author