The following T-SQL shows how to reset a tables identity auto increment back the last available number.
This may be useful if you have removed a bulk amount of data and need to reuse the ID again. For example, by mistake did a bulk insert into the wrong table and now need to undo the damage. It’s not something I would regularly do – or without considering the consequences – e.g. have those ID’s been referred to in other places?
In this example, we have a table with values up to ID 8 – but previously had used up to 13.
We want to reset the ID so the next used is 9.
Important: ALWAYS BACKUP before running bulk action commands.
DBCC CHECKIDENT ( Name_Of_Table, RESEED )
