How to RESET identity columns in SQL Server

Introduction

During application development, we often input dummy data into our database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the truncate command. This will delete data from table and also reset the identity column value to 0.

Solutions

One way is...

truncate table [table_name]
-- for example
truncate table product

But the truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

The other way is...

In this case, first you need to delete data from the child and the master table.

After deleting data, fire this command and it will reset your identity column to 0.

 

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
DBCC CHECKIDENT('product', RESEED, 0)