The Revolution Weblog: How to reset and Identity seed in SQL Server

Wed 16
August
2006

How to reset and Identity seed in SQL Server
Posted At : 8:46 AM | Posted By : tony

Searching today for my own good and use here at Navtrak, I found a simple little SQL Script that reseed's an identity column down to any value you choose.

Now, this will SERIOUSLY mess your application up if you are using the identity column as your ID in a Foreign Key elsewhere. However, in this case where I am generating unique serial numbers it works just fine. We started at 0, and through testing it jumped up to 250. Production wanted to start at 0 and for that reason we used this:

DBCC CHECKIDENT('uniqueSerials', RESEED, 0)

Comments (3) | del.icio.us | Linking Blogs

Related Categories: ColdFusion, Code

Comments

Nice tip. I've never see that before. Sure beats dropping and recreating the table.

Posted By Student Management Guy / Posted At 8/17/06 2:48 AM

You can also accomplish this by using TRUNCATE TABLE [tablename]. Obviously this will remove all the data in the table. However if you are moving from a test enviroment into a production enviroment, I'm pretty sure you don't want any test data in the table. Check the BOL for more information.

Posted By Tony Petruzzi / Posted At 8/17/06 5:37 AM

However if you are moving from a test enviroment into a production enviroment, I'm pretty sure you don't want any test data in the table. Check the BOL for more information.

Posted By self storage / Posted At 10/12/07 1:45 AM