When developing and testing an application we insert dummy data that is stored in a database. When the testing phase is complete and the application is ready to go live, the identity column of a table needs to be reset.
The methods used to reset the identity value are below.
Method 1
To reseed whole table staring from 0, the following code can be used
TRUNCATE TABLE tblnew
DBCC CHECKIDENT (tblnew,RESEED, 0)
It will delete the data from table and reset the identity column value to 0
Method 2
To reseed the table with its maximum id, the following code is used.
DECLARE @max_id BIGINT
SELECT @max_id = MAX(ID) FROM tblnew
DBCC CHECKIDENT (tblnew,RESEED, @max_id)
If you reset the identity with above code and insert a record it will start with the maximum ID plus 1. For example if the maximum ID is 25, the next value will be 26.
Method 3
To start numbering the identity column with an arbitrary number, then the following code is used.
DBCC CHECKIDENT (tblnew,RESEED, 100)
Using the above code, the value of the identity column of the next record inserted will be 101.
Note:
The above methods will work well if the identity column is not a primary key. If it is primary key first we need to truncate the table that referred as foreign key of particular column and then we can reset the identity.