Reseed SQL Server Identity Field
When I am populating a data snapshot table with a set of calculations for any given month, I may in some cases blow away any existing records for that month and start with a clean slate.
The only thing that bothered me was that I would end up with huge gaps in my identity keys. This resets the identity’s seed value, so that the next inserted row will pick up right where the last identity field left off.
I use this a lot for my data snapshot tables, but as a general rule, it’s not a good idea to reseed an identity column in a production table without some serious checks and balances.
Here is the statement, where the item in single quotes is the name of the table you want to RESEED:
DBCC CHECKIDENT (‘my_report_table’, RESEED)
You can also explicitly set the next seed value with this statement:
DBCC CHECKIDENT ('my_report_table', reseed, 1001)