Combine ALTER Statements
When making multiple modifications to a table, the generated SQL script contains multiple ALTER statements. For example, If I were to add 3 new columns to a table, the resulting script would be something like...
ALTER TABLE table ADD COLUMN newcolumn1 INT;
ALTER TABLE table ADD COLUMN newcolumn2 INT;
ALTER TABLE table ADD COLUMN newcolumn3 INT;
... when these could actually be combined into a single ALTER statement like...
ALTER TABLE table
ADD COLUMN newcolumn1 INT,
ADD COLUMN newcolumn2 INT,
ADD COLUMN newcolumn3 INT;
The 3 separate ALTER statements essentially take 3 times longer to run than the single combined ALTER statement. It might not seem like much, but this actually makes a gigantic difference when the table being altered is large, has many indexes, and/or has several things being altered... Imagine a large table that takes an hour to perform an ALTER on, and then having to unnecessarily multiply that hour by the number of things you're altering.
The same thing happens when doing a schema comparison.
gazuga
shared this idea
-
Sean Nelson commented
Yes yes yes! A thousand times yes! I have been manually editing scripts for this, too. It costs me like an hour of time to manipulate the script, but shaves several hours off the execution time in my case. Please make this an option.
-
John Carew
commented
I'm glad someone else also notices this, this is a MUST for large tables. We have a table with 147mil records, it takes hours to rebuild a table caused by a alter statement. It's getting tiresome having to manually edit the script before hand to do this.
-
Anonymous
commented
At least make it an option to combine ALTERS into one statement.