[dubeyniraj2010]

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]

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]

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]

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.