How should I set id to auto increment to reset when I delete record

I won't say this is not a good practice rather I will say it is a really bad practice.

But if you do want it for some unknown reason then

Delete from YourTable where PrimaryKeyColumnName=5;
SELECT * into #temp from YourTable ;
TRUNCATE TABLE YourTable ;
SET IDENTITY_INSERT YourTable ON;
INSERT INTO YourTable (PrimaryKeyColumnName,Col2,col3,other_columns)
SELECT ROW_NUMBER() over(order by PrimaryKeyColumnName),Col2,col3,other_columns FROM #temp
SET IDENTITY_INSERT YourTable OFF;

the above solution will work for all scenarios meaning if there are rows 1,2,3,4 and you delete row 2 then next when you will insert a row it will be 1,2,3,4.

if you just want the last record to delete and then reseed the identity then use DBCC_CheckIdent() instead.

Meaning, that if you delete the fourth row then it will start again from 4.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Yelve4y ago
The DBCC CHECKIDENT management command is used to reset the 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
0
Sachin Singh4y ago
I won't say this is not a good practice rather I will say it is a really bad practice.
But if you do want it for some unknown reason then
Delete from YourTable where PrimaryKeyColumnName=5;
SELECT * into #temp from YourTable ;
TRUNCATE TABLE YourTable ;
SET IDENTITY_INSERT YourTable ON;
INSERT INTO YourTable (PrimaryKeyColumnName,Col2,col3,other_columns)
SELECT ROW_NUMBER() over(order by PrimaryKeyColumnName),Col2,col3,other_columns FROM #temp
SET IDENTITY_INSERT YourTable OFF;

the above solution will work for all scenarios meaning if there are rows 1,2,3,4 and you delete row 2 then next when you will insert a row it will be 1,2,3,4.
if you just want the last record to delete and then reseed the identity then use DBCC_CheckIdent() instead.
Meaning, that if you delete the fourth row then it will start again from 4.
0