Ordering columns in database tables

Question

When it comes to column order in DB tables, are there any standards or at least best practices?

Here's a handmade convention that I follow:

• primary key (i.e. id);
• unique columns (i.e. email, ssn);
• foreign keys (i.e. article);
• columns holding user generated data (i.e. first_name, last_name);
• columns holding system generated data;
  • non-boolean (i.e. password_hash);
  • boolean (i.e. deleted, verified)
• timestamp columns (i.e. created_at);

These leave many questions unanswered, though, so I'd like to hear your thoughts.

Answer 1

In short, you've stated the standard conventions well and you're not missing a lot. IMO, the only move that would make someone look unprofessional would be not having the Primary Key(s) first. Having the foreign keys come right after that is a nice convention, but not a big deal. (Multi-field primary keys that include foreign keys should of course be at the very beginining, or someone should be beaten.) I would add two additional thoughts:

1. Have fields with similar themes near each other. Having City/State/Zip fields widely separated would be unhelpful, for example. I think it would not matter in the slightest whether user_role or user_ip came first, but they sound like they should be next to each other.
2. Secondary to other such conventions, it doesn't hurt for things to be alphabetical.

Having additional conventions within your database is a very good idea (like as you mention always having the timestamp at the end). If you have ChangeDate and ChangeBy fields in a lot of your tables, having them (obvously next to each other and) consistently located is good.

Additionaly, ErikE mentioned that there can be some efficiency to having, at the end of your table, the variable length fields (varchar, nvarchar) that might often contain nulls. Other than that, I don't think there are any performance advantages to arranging things a certain way in modern relational databases.

Naming

Often when you're deciding column order is the same time you're deciding on column names, so I'd like to address that a little. You can certainly make horribly, costly mistakes with the naming of your fields; this is much more important than your column ordering. Ordering can be changed easily, but poor names will cause you problems forever. It's a huge pain to change table/column names a year later when there's dozen's of references to them. I just added an answer here to address this very important topic.

Answer 2

In MSSQL Server, NULL columns at the end of the column list actually reduce the space required to store that row, which can increase the number of rows per page, which can reduce the number of reads required per I/O operation, which is a performance benefit. While the performance benefit may not be huge, it is something to keep in mind for any column that has a preponderance of NULL values.

Proof of trailing NULLs reducing storage space can be had at Deciphering a SQL Server data page:

... The null bitmap is slightly different (fe / 1111 1110) since it's now the second column that's null. What's interesting is that in this row, only a single variable length column is present, not two. Thus there's only a single variable length column end index identifier, 0d00 / 0x000d / 13. From that we can conclude that columns are handled in order, and thus one might want to consider the order of columns, if a specific column is usually null, it might be more efficient to have it ordered last.

Note that this applies only to variable-length columns. While that clearly includes varchar, varbinary, and so on, I'm not sure about other data types (and don't have time right now to conclusively determine this).