Manually Reset Identity Column Value in SQL Server

Problem : If you are creating any table and using an identity column to that table, then at first time, when you insert any row, then identity value will start like 1,2,3... and so on..

But if you delete all the row from the table and when you try to insert new row in same table, the identity value will start from last identity value.

For Example the last identity was 6, so after deleting all data, when you insert new row, the identity value will be 7.

So to reset the identity Column value, you can use this below code.

Syntax

DBCC CHECKIDENT ( <table name>,RESEED,<new value>)

QUERY 

DBDD CHECKIDENT ('tbl_rajesh',RESEED,1)

Explaination :
table name = Your table name should be entered here
RESEED = this is used to reset your identity value.
new value = Starting identity value

If you want to check current identity value of any table, you can use below query.

DBCC CHECKIDENT (’tablename’, NORESEED)

Explaination :
table name = Your table name should be entered here
NORESEED = This will avoid RESEED function.

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com