Resetting an Auto Incremental Primary Key on SQL Server

Almost all of us create tables in SQL Server with auto incremental primary keys. After creating these tables we write programs which use these tables to place data in them. These programs under go a  lot of testing and thus place a lot of test data in these tables and in turn increment the value of such auto increment fields.

Once all the testing is said and done and you are all set to launch your program/application/software you empty the database/tables so that there is no dummy/test data. but the simple delete statement:

DELETE FROM [tableName];

only clears the data but does not reset the values of any auto increment  fields, this gives a sense of uncleanliness when you are delivering the product/project.

So to reset any auto incremental primary key in MS SQL Server 2008 user defined database tables use the following command after the DELETE statement above.

DBCC CHECKIDENT ("dbo.[tableName]", RESEED, 0);

After the command has executed the next value generated for the auto incremental primary key would be 1, hence the count has been reset, giving a sense of a fresh start or a new beginning. Now you can test a fresh or deliver it a fresh. As pizza companies say Oven Hot we developers can say Database Fresh :).

Happy resetting. :)