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 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).

Answer 2

you can find different best-practices allong the net.

Always save CREATE TABLE statements, along with all other statements defining database schema in a secure location. Every time you make a change to a database object, be sure to script the change and check it into version-control software, such as Visual Source Safe.

With such policy you can easily re-create database schema on the same or different server, if necessary. Also, if you have the same database on multiple servers, it's easy to compare schemas and reconcile any differences that might have crept in over time.

Although descriptive, table names have no performance benefits. They make databases self-documenting and easier to code against. Table names should reflect their business meaning.

Create user tables on a non-primary filegroup; reserve the primary file group for system objects. This way the system supplied and user-defined objects do not compete for disk resources.

Create commonly accessed tables on the same filegroup. You can expect performance benefits if the data of commonly joined tables resides on the same disk.

Create a clustered index on every table. Each table can only have a single clustered index. If a table has a clustered index, its data is physically sorted according to the clustered index key. Clustered indexes in SQL Server have numerous benefits. For example, if you retrieve data from a table using an ORDER BY clause referencing the clustered index key, the data does not need to be sorted at query execution time.

If two tables have a common column, for example customer_id, and both tables have clustered indexes on customer_id column joining, such tables will be considerably more efficient than joining the same tables based on the same column but without clustered indexes.

Ensure the clustered index is built on a column that contains distinct

Source: Creating SQL Server tables: A best practices guide

Answer 3

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 4

In MS Sql Server, datatypes ntext, image, and text (all recently deprecated) should be the last columns in the row to avoid a performance penalty.