Have you deleted a bunch of rows from a table that has an identity column value? Do you now find that when you insert a new row, you find that the identity column value was set to one higher than the highest identity column value that your table original had? This can happen if you make a mistake and inserted a bunch of rows by accident, and then you delete those accidentally insert rows without reseeding your identity value.
If you have made this insert mistake or just delete a bunch of rows at the high end of the identify column value range, then you might want to reseed the identify column value, prior to adding any more rows. If you don’t reseed your identity value, then you might find that you have large gaps in your identify column values.
In order to reseed the identity column values, you can run this command:
DBCC CHECKIDENT ('youtable', RESEED, <new seed value>)
Where “<new seed value>” is set to the highest identify value in your table.
After running the DBCC command your next inserted row into your table will be inserted with an identity column value 1 higher than the new seed value specified in the DBCC command.
You must use this command with care. Do not set the new seed value to be less than the highest identity column value in the table. If you set your new seed value to be less than the highest identity value in your table, then you might end up with SQL Server trying to insert a new identity value that has the same value of as an identity value already in your table. When this happens your insert statement will fail; therefore, be careful when you reseed the identity value on your tables.
# # #
See All Articles by Columnist Gregory A. Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.
