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.
Popular search terms:
