July 14, 2010 at 1:53 pm
I am using SSMS 2008 and went to add some new columns to a table.
I can do this in the design view, but why can't I script an ALTER statement?
Just adding new columns shouldn't require the table to be dropped or suffer any "massive changes"?
I Googled a bit and it said if I use design it will copy all the data to a temp location, drop, then recreate the table.
So I assume it will preserve all PKIDs, but what if I had a 16 TB table, that would be a MASSIVE disk I/O operation...
Am I missing something here?
July 14, 2010 at 3:02 pm
Script Table As -> ALTER To - grayed out??? Why?
Probably because the only portion that could be sensibly scripted out without further knowing what you're doing would be
ALTER TABLE <Table name>
The rest of the command completely differs depending whether you want to add columns, alter a column, drop a column, add a constrain, drop a constraint, enable or disable a trigger, enable or disable change tracking, switch a partition, rebuild the table or set a table option.
I Googled a bit and it said if I use design it will copy all the data to a temp location, drop, then recreate the table.
So I assume it will preserve all PKIDs, but what if I had a 16 TB table, that would be a MASSIVE disk I/O operation...
Which is why most people will write the alter table statement themselves and leave the designer where it should be - closed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2013 at 12:46 pm
Wonder why it's even on that menu anyway, then...??
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply