T-SQL – How to reset auto increment to next available number

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.