asked
Reset Identity Seed
I have been working in a development database on an ETL process where the table has an identity seed column. After running the ETL process multiple times truncating the table after each run I have noticed that my identity column is starting at a much higher number. It is acting like it is keep count of all the previous rows. How can I reset this to start at zero? I am working with SQL 2005 SP3.
Thanks everyone. @Slick84 and @kevin feasel you are correct, I had been using delete tablename instead of truncate table tablename. Using truncate resets the identity seed too. @trad, thanks for your quick response and explanation as well.
@BradleySQL, first of all the database isn't acting, it does in fact keep a tally of all the previous inserts by design. The simple way to reset (reseed) the identity column is to DBCC CHECKIDENT('tablename', RESEED, 0) With 0 being the number you want to start the identity back off with. For instance if you want the value of the next row to be 101, then replace 0 with 101. I hope this helps.
Thanks for the explanation, this is exactly what I was looking for.
dbcc checkident('[SchemaName].[TableName]', 'reseed', -1); The next record inserted will have a value of 0. Also, if you do not have any foreign keys pointing to this table, instead of just doing a "delete from [table]," you can truncate the table with: truncate table [SchemaName].[TableName] This will reset the identity key to whatever you originally set it to on the initial creation.
Thanks, no foreign keys on the table, I have been doing truncates after the ETL load when I needed to test it again. That is what got me in this situation of needing to reseed the table.
Are you saying you are issuing the truncate table statement but the identity column is not resetting to zero?
Correct. My identity column keeps a running tally. Example, it started at zero, I insert 1000 rows, truncate the table and run the insert again, it starts at 1001. using the command @trad and @kevin feasel listed I can reset it to zero and start over. Not that it is critical to do so in my dev environment but I wanted to know how to do it.
Your identity values **should reset to zero automatically when doing a TRUNCATE.** The reseeding procedure is only used when running a DELETE statement which does not reseed. [
http://msdn.microsoft.com/en-us/library/ms177570(v=sql.90).aspx][1] FROM MSDN: If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead. [1]:
http://msdn.microsoft.com/en-us/library/ms177570(v=sql.90).aspx
