Column order had a big performance impact on some of the databases I've tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb:
- Primary key columns first
- Foreign key columns next.
- Frequently searched columns next
- Frequently updated columns later
- Nullable columns last.
- Least used nullable columns after more frequently used nullable columns
An example of the difference in performance is an Index lookup. The database engine finds a row based on some conditions in the index and gets back a row address. Now say you are looking for some value, and it's in this table:
SomeId int,
SomeString varchar(100),
SomeValue int
The engine has to guess where SomeValue starts, because SomeString has an unknown length. However, if you change the order to:
SomeId int,
SomeValue int,
SomeString varchar(100)
Now the engine knows that SomeValue can be found 4 bytes after the start of the row. So column order can have a considerable performance impact.
Note: Sql Server 2005 stores fixed-length fields at the start of the row. And each row has a reference to the start of a varchar. This completely negates the effect I've listed above. So for recent databases, column order no longer has any impact.