Reset SQL Server Identity Fields

A lot of times after doing extensive testing with a website and its corresponding database, I find it annoying that the identity fields (those commonly called the auto-increment or autonumber fields) have run up their increment to a pretty high number, even though I have deleted all the rows out of them. So, after some research, I stumbled upon a way to reset it back to zero without dropping the table. As a side note, this method is valid for both SQL Server 2000 and 2005.

First things first, pull up a query window inside of SQL server. Then, run the following query and modify tablename to fit the particular table that needs its identity reset:

DBCC CHECKIDENT(tablename, RESEED, 0)

Go ahead and execute the query and your identity fields should be good to go!

Posted on Dec 18
Written by Wayne Hartman