February 27, 2011 at 9:38 pm
I have a big Table with 100+Gb data. I need to add identity column into it. I was trying with Alter command but it is taking huge time. Is there is any workaround for this.
February 27, 2011 at 9:48 pm
Amit, not really. You're basically forcing an update to every row in the table.
You can do some things that will help with availability, like making a copy of the table and then swapping them at the end of the process. You'll have to keep track of any data changes made in the meanwhile, of course.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 27, 2011 at 9:51 pm
Create new table with same architecture and with your new column.
Then import the data from old table to this new table and dont import the data in once, keep inserting the record in chunk of 10000 records or as per capacity. I know you have a big table then you can schedule the insert task in late night hours when you server is idle/not much busy.
Once import complete then you can rename your new table with old name and old table with some name as oldtbl_arch. Also you might find out that is it worth to import all data from old table to new table.
----------
Ashish
April 13, 2011 at 11:03 pm
Another case
I have a big Table with 100+Gb data. I need to change one column datatype from varchar 200 to varchar max. I was trying with Alter command but it is taking huge time. Is there is any workaround for this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply