20 August 2010

How to change the identity value of a table

To change the identity value of a table use the statement:

DBCC CHECKIDENT (myTable, reseed, value)


Where
- myTable is the table to change the identity value
- reseed specifies that the identity value should be changed
- value is the new value to use for the identity column

Example:
DBCC CHECKIDENT (Products, reseed, 12)

After this command executed, the next identity value of the Products table will be 13

This command is useful if you delete records at the end of a table and want to keep the identity values sequential.