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
Banned
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

DECLARE @max_id BIGINT
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

TRUNCATE TABLE tblnew
DBCC CHECKIDENT (tblnew,RESEED, 0)

above the case only the ID column is not a primary key
mountainman's Avatar
Banned
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.