|
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.
(comments are locked)
|
|
@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.
Mar 31 '11 at 10:41 AM
BradleySQL
(comments are locked)
|
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: 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.
Mar 31 '11 at 10:42 AM
BradleySQL
(comments are locked)
|
|
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 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.
(comments are locked)
|
|
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.
Mar 31 '11 at 11:12 AM
BradleySQL
(comments are locked)
|
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.