SQL Server Performance Forum – Threads Archive

How to reset the identity value

Hi,
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:http://www.sql-server-performance.com/q&a118.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. http://www.sqlpass.de)

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=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Madhivanan/Frank,
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

]]>