Quick question, I was curious if there is any difference between a database table that has been defined in one shot, and one that has had columns added to it over time. Does the one with added columns performance suffer in someway?
Anyways database vendor isn't too relevant here unless there are vendor differences for this case.
Thanks
That's going to be completely storage engine dependent. Generally I'd feel perfectly comfortable with added columns, maybe after throwing an OPTIMIZE TABLE at them.
There are vendor differences here. The SQL language is defined by a standard, but storage details are left to each vendor to implement.
For example, in MySQL when you add a column, the database engine copies the entire table, making space for the new column on each row. Once it has copied all rows, it drops the old copy of the table. Thus the new table is stored exactly as if you had defined it with the new column from its inception. But you have to wait for the copy to finish before ALTER TABLE returns (which can take a long time if the table is huge).
In another brand of database (although I don't have one in mind), adding a column might store the extra column(s) separately from the original table. This would help make it very quick to add new columns, but retrieving data would be affected by the disconnected sections of data. Presumably you could later tell the database engine to "defragment" the table and bring all the columns together.
In Oracle you can add empty columns to an existing populated table and essentially nothing will happen as a result other than the logical redefinition of the table structure. When you populate those columns though, which might hapen at the time the column is added of course, then the rows have to extend and you may hit a problem with some rows not having enough free space in the blocks that contain them, in which case they will be migrated to a different block with a pointer left in the original location (thus indexes are not affected as they continue to point to the original location).
Because of this if you are adding populated columns to a table with rows in it then you might want to rename the original table and perform a "create table as select" to create and populate the new version. Indexes, triggers, privileges etc will of course also have to be migrated. If the table is not too large, however, you could add the populated columns, taking the performance hit due to the row migrations, and then perform a table move to reorganise it.