Sql-server – Reset AutoIncrement in SQL Server after Delete

auto-incrementdelete-rowsql server

I've deleted some records from a table in a SQL Server database.

The IDs in the table look like this:

99
100
101
1200
1201…

I want to delete the later records (IDs >1200), then I want to reset the auto increment so the next autogenerated ID will be 102. So my records are sequential, Is there a way to do this in SQL Server?

Best Answer

Issue the following command to reseed mytable to start at 1:

DBCC CHECKIDENT (mytable, RESEED, 0)

Read about it in the Books on Line (BOL, SQL help). Also be careful that you don't have records higher than the seed you are setting.