21 December 2010

Reseed a table in SQL Server

In Microsoft SQL Server, it is possible to reseed an identity column of a table. If you want to start an identity column at a different value (for instance, after clearing it), you can use the following syntax:
DBCC CHECKIDENT (myTable, reseed, 0)
The next row that is added to the table will have 1 in its identity column. If we would use
DBCC CHECKIDENT (myTable, reseed, 41)
the next row would get 42 in its identity column.

Be careful when using this on tables with rows in them, if the new seed is lower than the highest currently in the table, you will encounter problems before long. When the new seed is already in use, you will get a unique key restriction violation on insert!

No comments :