Is there a best practice to database column ordering?

Question

Are there any best practices to column ordering when designing a database? Will order effect performance, space, or the ORM layer?

I am aware of http://stackoverflow.com/questions/34818/sql-server-does-column-order-matter. I am looking for more general advice.

Answer 1

I try to stick with the most important columns first. Typically I always keep my ID column as the first in any table. Then whatever information is important and is updated frequently usually follows, then the rest which may or may not be updated frequently.

I don't think it will affect performance, but from a developer stance, it's easier to read the first few columns which will be updated frequently than try and scan the hole table for that one field at the end.

Answer 2

I don't believe that the column order will necessarily affect performance nor space. To improve performance, you can create indexes on the table, and the order of the columns defined in the index will effect performance.

I've seen tables have their fields ordered alphabetically, as well as "logically" (in a way that makes sense for the data that is being represented). All in all, I can see benefits in both, but I would tend to go for the "logically" method.

Answer 3

I think the answer is no.

RDBMS servers optimise these kinds of things internally for queries so I suspect it's unimportant.

Answer 4

column order only matters in a composite index

If your index is on ( Lastname, firstname) and you always search for last name then you are good to go even if you don't include first name

if your index looks like this (Firstname, Lastname) and your where clause is

where lastname like 'smith%'

then you have to scan the whole index

Answer 5

More general advice isn't really available since you're asking for implementation details rather than the SQL standard.

Different DBMS will implement these things differently.

However, a clever DBMS would implement the internals such that the column ordering is not of consequence.

Therefore, I would order my columns to be intuitive for human readers.

Answer 6

In designing a database, I would probably put the most important columns first in a logical order (idfield, firstname, middlename, lastname for instance). It does make it easier to see them when you are looking for the columns you need the most out of a long column list.

I would however not rearrange the columns later on to support a more logical grouping.

Answer 7

In Oracle there can be significant storage space savings if your table has a number of NULLable columns and you place the NULLable columns at the end of the list. NULL values on the end of a row take up no space.

e.g. imagine this table: (id NOT NULL, name VARCHAR2(100), surname VARCHAR2(100), blah VARCHAR2(100, date_created DATE NOT NULL)

the row (100, NULL, NULL, NULL, '10-JAN-2000') will require storage for the values 100, some space for the three NULLs, followed by the date.

Alternatively, the same table but with different ordering: (id NOT NULL, date_created DATE NOT NULL, name VARCHAR2(100), surname VARCHAR2(100), blah VARCHAR2(100))

the row (100, '10-JAN-2000', NULL, NULL, NULL) will only require storage for the values 100 and the date - the trailing NULLs are omitted entirely.

Normally this makes little difference but for very large tables with many NULLable columns, significant savings may be made - less space used can translate to more rows per block, meaning less IO and CPU required to query the table.