MS SQL - Reset Table Identity Value (DBCC CHECKIDENT)

With DBCC CHECKIDENT you can change Next Identity Value of a table. Let us consider an  example, the table 'Orders' has reached identity value of 860 and your first financial year is finished. Now you wish to start order numbers for next year from 1001. One can do so with DBCC CHECKIDENT - RESEED option. 

However if you reset Identity Value below current value then next insert statement will try to insert duplicate value in table and will violate the duplicate key unique on Identity Column, hence will throw resultant error.

If you have deleted all data from table and wish to start numbers from 1, you should reseed identity value to 0 for given table.   

Check current identity value of a table using 

Command:

SELECT IDENT_CURRENT('table name')

Example:

SELECT IDENT_CURRENT('Orders')

Result:

860

Then you can choose a reseed value for your table and execute following command.

Command:

USE <database name>
GO
DBCC CHECKIDENT (<tablename>, RESEED, <seed value>)

OR

DBCC CHECKIDENT (<tablename>, RESEED)

<seed value> is optional.

Example:

USE Inventory
GO
DBCC CHECKIDENT ('Orders', RESEED, 1000)