Fastest way to alter table structure

Question

I'm writing a database table designer (works like the Enterprise Manager one, but with restricted datatypes and some checks), and I have a bit of a question:

To alter the structure of a table, I'm currently using the same method as Enterprise Manager (create a temporary table with the new schema, copy the data across, drop the original table, rename the temporary table).

When altering the structure of a table with a large number of rows, but not changing the order of columns or adding columns in the middle of the table:

• Would it be faster to execute multiple ALTER TABLE statements?
• If so, at what point would it become slower? (Say, changing lots of column data types, etc).

Thanks

Answer 1

Probably always faster using ALTER TABLE.

I really doubt that ALTER TABLE statements would be noticably slower.

Answer 2

Thanks.

I was worried that if there were a lot of column data type changes there could be a lot of conversions, and a lot of overhead from going through the entire table multiple times.

Answer 3

I guess that could happen -depends upon the quantity and type of changes.