February 13, 2013 at 11:10 am
Running SQL Server 2008 R2.
I have a rather large table (122M records) and I need to add a new column - not just to the end, but nestle the new column amongst the others. When I do this I get the standard warning about large amounts of data, then I get a timeout expired message . I'm assuming this is due to the size of the table and it has to create a temp table and copy data (I don't understand all the exact details of how the engine does this).
What I've done is create a new table that contains the new column. I then go into Import\Export Wizard and attempt to copy the data. However, the new column is not showing up in the destination table. What am I overlooking?
Also, if there is a better way to accomplish adding a new column to a [rather] large table, please let me know.
Thank you!
February 13, 2013 at 12:10 pm
Kevin Drysdale (2/13/2013)
Running SQL Server 2008 R2.I have a rather large table (122M records) and I need to add a new column - not just to the end, but nestle the new column amongst the others.
Why?
When I do this I get the standard warning about large amounts of data, then I get a timeout expired message . I'm assuming this is due to the size of the table and it has to create a temp table and copy data (I don't understand all the exact details of how the engine does this).
The Table Designer has a timeout of 30 seconds. You can change it but bottom line is you should not attempt to save the large-scale changes using the Table Designer. Use the Script button to get the T-SQL the GUI would run if you clicked Save and then run that in a Query window.
Also, if there is a better way to accomplish adding a new column to a [rather] large table, please let me know.
Yes. Simply use ALTER TABLE to add a new column to the end of the list of columns in the table. SQL Server cares not about the order of the columns in terms of how the data is stored and the order of delivery to a data client should be abstracted through an application data layer anyway, so why bother maintaining an arbitrary order of columns?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply