August 9, 2013 at 2:51 pm
I need to reset the identity seed on 7.5 million records (someone added a need identity seed to an archive table instead of using the one that's assigned in the production table - now the archive table is out of sync with the production table). I want the last ID in the archive table to bump up against the first ID in the production table, that way moving and deleting records from production to archive will be easier. However I'm wondering how to reseed 7.5 m records without blowing up my transaction log. Any suggestions? TIA!!
August 9, 2013 at 3:08 pm
SQL has a command to do that directly:
DBCC CHECKIDENT ( <table_name>, RESEED, <new_reseed_value> ) --WITH NO_INFOMSGS
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 9, 2013 at 3:23 pm
If you mean that you want to update the values explicitly, forget it. You cannot update values in an IDENTITY column.
Nor should there be any need to. Or more precisely, if there is a need to, you should never have used IDENTITY in the first place. It is an inherent property of IDENTITY they are meaningless value. You may rely on that they are monotonically growing (but that is not without risk). Gaps will always be there. It is part of the design.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 12, 2013 at 8:17 am
I could not agree with you more Erland. Unfortunately this database was recently handed off to me. The other administrator had an identity seed in the production table, when he moved data to an archive table he did not leave the identity as it was originally set, he started a whole new identity and now the MAX identity in the archive table is greater then the MIN identity in the production table. Since the identity in the archive means nothing at this point, I'd like to reseed it so it will now match up with my production table. Then I plan on turning off the identity in the archive table and using what is originally assigned in the production table.
August 12, 2013 at 8:28 am
One suggestion to keep the values in "sync" between the two tables, if I'm understanding things correctly, might be to just remove the Identity column from the archive table.
When you "transfer" the data from production to archive, treat the ID as any other value.
As for "re-seeding" the ID values in the archive table, you might find it easier to add a new column, insert the correct ID values (possibly if you need to keep it an Identity type, use "set identity_insert {tablename} ON" so you can put in your own values, then drop the old ID column, and rename the new.
But I think you'll still need to watch the log while doing this...
August 13, 2013 at 3:47 pm
Shaira (8/12/2013)
Then I plan on turning off the identity in the archive table and using what is originally assigned in the production table.
Note that you cannot turn off IDENTITY with less than rebuilding the table, so if you want to change the values to match up with the live table, you could just as well do that on the reload.
Well, there is no other choice anyway, since you cannot update an IDENTITY column.
Or you could just let it lie...
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply