How to reset the identity value | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to reset the identity value

How do I reset the identity value for a table that was truncated? For example, when I insert the records into a table and it generates the identity values (1, 2, 3) for each record. After truncating this table, I can’t get the same identity value next time when I insert the same records instead; it goes incremental from where it left off earlier(4, 5, 6 etc.). How can I reset the identity value from the beginning (1, 2, 3 etc) again without explicitly using IDENTITY_INSERT ON? Thanks. Dan
After truncating the table, execute the following: DBCC CHECKIDENT (‘table_name’, RESEED, 1) This should reset the identity value back to 1 – Tahsin
Thanks Tahsin for your prompt reply. Yes, it worked after setting the command as DBCC CHECKIDENT (‘table_name’, RESEED, 0) because I want the first identity value to be 1. Dan

I assumed that the first inserted column would get the value it was set to, but I guess SQL server adds a 1 to it. Thanks for the feedback.
Did you delete the table or truncate it? If you truncate the table, the identity column is reset Truncate table tableName Then no need to run DBCC CHECKIDENT Madhivanan Failing to plan is Planning to fail
You might want to check this out:
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?
Ich unterst�tze PASS Deutschland e.V.
Oops, sorry, just read my link again. I thought I’d also mentioned there what Madhivanan said. [<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=></a><br />Heute schon gebloggt?<a target="_blank" href=></a><br />Ich unterst�tze PASS Deutschland e.V. <a target="_blank" href=></a>)
My bad, I couldn’t truncate the table since it was referencing to other tables for FKs (that tables must not be truncated). So, delete was the only option to empty table. Thanks for the reply. Dan