In testing in a data warehouse environment, you often need to have large complicated slow tedious scripts to get you back into the exact same state for each test. I was using a large script a colleague of mine made when I noticed a very large part of the script was deleting and creating tables just for the purpose of setting the identity column back to the initial counter. Well MS SQL has a funtion to do just this:
Snytax:
DBCC CHECKIDENT (‘TABLE_NAME’, RESEED, NEW_SEED_NUMBER)
Example to set the idenity seed of table MikesDB.MikesSchema.MikesTable back to 1
DBCC CHECKIDENT (‘MikesDB.MikesSchema.MikesTable ‘, RESEED, 1)
MS SQL OUTPUT:
Checking identity information: current identity value ‘19844′, current column value ‘1′.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Filed under: Uncategorized | Leave a Comment
Tags: Reset Identity MS SQL Server
No Responses Yet to “reset identity counter in MS SQL Server”