Search code examples
sql-server-2008-r2auto-incrementidentity-column

Reset autoincrement in Microsoft SQL Server 2008 R2

I created a primary key to be autoincrement.

  • I added two rows: ID=1, ID=2
  • I deleted these two rows.
  • I added a new row, but the new row's ID was: ID=3

How can I reset or restart the autoincrement to 1?

Solution

  • If you use the DBCC CHECKIDENT command:

     DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1);

    But use with CAUTION! - this will just reset the IDENTITY to 1 - so your next inserts will get values 1, then 2, and then 3 --> and you'll have a clash with your pre-existing value of 3 here!

    IDENTITY just dishes out numbers in consecutive order - it does NOT in any way make sure there are no conflicts! If you already have values - do not reseed back to a lower value!