Resetting your Identity Column (SQL Server)
August 25, 2004 · 2 Comment s
Just a handy little bit of SQL for when you need to reset an Identity column back to it's initial starting value.
DBCC CHECKIDENT ('tablename',RESEED,0)
Useful for when you want to re-initialise your identity columns after testing. Tags: SQL Server
2 response s so far ↓
1 Steve Powell // Aug 26, 2004 at 12:01 PM
TRUNCATE table tablename
DBCC CHECKIDENT('tablename')
Which reseeds the IDENTITY column to the lowest value in the column left in the table. The truncate throws all the data away without logging it to the transaction log (so its not recoverable) but if you're scrubbing tables down frequently your transaction log can grow fairly quickly. So a truncate followed by a reseed should set it to zero.
You could get a problem if you reset it to zero while there's still data in the table. INSERTS will fail with IDENTITY CONSTRAINT failures if it trys to insert a value that's already present.
2 Pass saliva drug test // Aug 28, 2009 at 10:08 AM
Leave a Comment