sql-serverauto-incrementdelete-row

Reset AutoIncrement in SQL Server after Delete

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?

Solution

  • 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.