Combine ALTER statements
Posted: Fri 07 Jun 2019 19:43
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...
... when these could actually be combined into a single ALTER statement like...
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.
Code: Select all
ALTER TABLE table ADD COLUMN newcolumn1 INT;
ALTER TABLE table ADD COLUMN newcolumn2 INT;
ALTER TABLE table ADD COLUMN newcolumn3 INT;
Code: Select all
ALTER TABLE table
ADD COLUMN newcolumn1 INT,
ADD COLUMN newcolumn2 INT,
ADD COLUMN newcolumn3 INT;
The same thing happens when doing a schema comparison.