Applications

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)

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.