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

Question

I want ids to reset to normal anytime I delete record in the table

Vishal Yelve · Answer

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

Sachin Singh · Answer

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.

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

Answers (2)