Reset IDENTITY value

You can reset the identity value by

DBCC CHECKIDENT('tableName', RESEED, 0)

So next time you insert into TableName, the identity value inserted will be 1.

When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case.

Now when you truncate the table, it will reset the Identity value to its original Seed value of the table.

Refer to : SQL SERVER – DELETE, TRUNCATE and RESEED Identity for a detailed example and some good explanation of Difference between Truncate and Delete

Kin has shown you how you can reset the IDENTITY value, but outside of a development environment when you're really removing all of the data, why do you need to do this?

I hope you are not intending to maintain a contiguous sequence of IDENTITY values when you are in production. And I hope you aren't really writing your code to hard-code the IDENTITY values. If these are meaningful ID values then you should stop using the IDENTITY property.

There are a few things that will prevent this from happening:

• if an IDENTITY value is assigned during a transaction, and the transaction is rolled back, the value isn't "given back" and the next value will be the one never used + 1.
• if a row is later deleted, the IDENTITY never comes back to fill in the gaps.
• there is an active bug in SQL Server 2012 that won't be fixed until SQL Server 2014 will never be fixed (unless you use an undocumented and very expensive trace flag) whereby a restart will seem to discard up to 1000 values from your IDENTITY column. The bug on Connect suggests that this is restricted to failover events involving Availability Groups but I can assure you the bug is much more broad than that.

In short, if you care about gaps or wish to give these values specific meaning, stop using IDENTITY. Drop and re-create the table and when you need to delete the values and re-populate, either perform an update, or perform an insert with hard-coded values for that column.

As an aside, primary key and identity are not the same thing. An identity column is not a primary key unless you explicitly define it as such, and you can certainly have a primary key that is not an identity column.