locked
Fastest way to alter table structure RRS feed

  • 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
    Thursday, June 14, 2007 1:34 AM

Answers

  • Probably always faster using ALTER TABLE.

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

    Thursday, June 14, 2007 1:38 AM

All replies

  • Probably always faster using ALTER TABLE.

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

    Thursday, June 14, 2007 1:38 AM
  • 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.
    Thursday, June 14, 2007 3:27 AM
  • I guess that could happen -depends upon the quantity and type of changes.
    Thursday, June 14, 2007 6:02 PM