Wednesday, November 10, 2010

Inserting Records with Identity column table

So, recently, I needed to insert some static data manually within the staging environment.  I wanted to align the identity column values to be in ascending order but if there are any typos causing my inserts to be rolled back, then the identity values will have gaps because rollback operation does not preserve the next value for identity.  Therefore, using DBCC CHECKIDENT ('<schema>.<tbl_name>', NORESEED), I can find the current value of the seed, while using DBCC CHECKIDENT ('<schema>.<tbl_name>', RESEED,<last_used_value>) sets the identity to the last value.