dubeyniraj2010's Avatar, Join Date: Jun 2011
Newbie Member
its recommended to delete all the records from the table, or it may create a duplicate values, if identity column is not a primary key.

then use DBCC CHECKIDENT as follows to reseed an identity column
let say tran type is the table name I want to reseed to 0, then I will call following command.

dbcc checkident
tran_type, reseed,0
junespring001's Avatar
Same problem I have encountered, I need help too... I have checked back the codes but it doesn't work. Did I missed something?
sql-programs's Avatar, Join Date: Oct 2009
Go4Expert Member
The following query only reset the value from last record on wards. but not reset the middle records

SELECT @max_id = MAX(ID) FROM tblnew
DBCC CHECKIDENT (tblnew,RESEED, @max_id)

You need to table identity column reset then run the following query. It can delete the all records from table and reset the identity column value


above the case only the ID column is not a primary key
mountainman's Avatar
As i know that, We can use identity on a primary key column is convenient. If we have other ways of upholding a primary key that is fine, if not even preferable.