October 13, 2004 at 8:31 am
Can anyone please let me know if we can reset the identity value to what it was before. I have a scenario of a table with an identity column. If i delete all rows of that table and then add again what value gets assigned to identity column. is theer a way to assign it the first valyue that it had.
any help will be greatly appreciated.
TIA
October 13, 2004 at 8:40 am
Try DBCC CHECKIDENT using the RESEED option.
Darren
Darren
October 13, 2004 at 10:45 am
Beware that the values are not necessarily unqiue. If you delete a partial range and reset the identity values, then you may or may not overwrite other values. Also, if you setup a uniue index on the identity column, this does not affect the automatic calculation of the next value, it won't skip values.
October 13, 2004 at 9:43 pm
I believe if you use the TRUNCATE TABLE statement you will reset the identity column to the original seed value. The TRUNCATE TABLE statement can only be used to delete all rows. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
October 14, 2004 at 1:31 am
Maybe I'm the one who didn't understand the question correctly, but it seems to me that all the replies are trying to answer a different question. I understood it to be "If we delete all rows from a table, and then add the same rows again, is it possible to assure that each row will have the same ID as before it was deleted?"....or am I wrong?
If that's so, I'd say that the only way how to do it is to store all records in a temporary table along with their original ID before deleting them. Then you can use SET IDENTITY_INSERT ON for the table in question, and insert the records including their ID's. I'm not quite sure why you'd want to perform that operation (deleting and inserting back again), but it can be done.
HTH, Vladan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply