October 21, 2016

Reseeding SQL Server Identity

Reseeding / Resetting identity value in SQL Server 2005

You can reseed indentity values, that is, to have identity values reset or start at new predefined value by using DBCC CHECKIDENT.  For example, a table named nwdsTable can be reseeded with the indentity column to 3.

 dbcc checkident (nwdsTable, reseed, 3)

 To reseed tables to start with identity of 1 with next insert , reseed table's identity to 0.  Identity seed is what the value is currently at, meaning that the next value will increment the seed and use that.  However, keep in mind  if you set the identity seed below values that you currently have in the table, that you will violate the indentity column's uniqueness constraint as soon as the values start to overlap