Reset auto increment in Sql Server tables after delete records (Without truncate table)

In this post i'll describe how to reset auto increment in sql server tables  after delete records.

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 following command.

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

if we entered [new_reseed_value] as 0 then in the next insert the auto increment field will contain value 1

if we entered [new_reseed_value] as 100 then in the next insert the auto increment field will contain value 101