Search code examples
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 ID 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.