Is there a standard/convention for the ordering of columns in a database table definition?

Question

Is there a standard/convention for how the columns should be ordered in the definition of a database table, and if so what is the motivation for that standard? (pros/cons)

For example, should the primary key be the first column? Should the foreign keys directly follow the primary key or should they be placed at the far right of the table?

If there is a difference between conventions for different DBMSs, the DBMS in this case is Microsoft SQL Server 2005.

Thanks /Erik

Answer 1

I'm not sure about set conventions but we always put the ID/Primary key column as the first column in the table. I suppose it's just because it makes it clearer to see the pk. I'd say also follow that with fk fields but after that there's no real standard, you could maybe do it by datatype?

Also, this isn't a set out best practice, just a personal choice.

Answer 2

I am not aware of any standard, but the way we structure our colums is

1. Primary key(s)
2. Any foreign keys
3. Data

Our large data columns, like comments, are put at the end. It makes it easier to view as much data as possible in the Query Analyzer without having to scroll to the right.

Answer 3

mm.. As I know there are no limitations. Its just a matter of clarity.

Answer 4

None that I know of. You can always re-order the columns during a select anyway, so it hardly matters unless there's some truly obscure optimisation you can do on some databases by ordering your columns appropriately.

Answer 5

THere is no official standard that I'm aware of, but the normal practice is to put the primary key first (it's usually a surrogate) and then group the other fields by functionality. So in a "person" table one would group the fields containing the person's address data together. Most people add FKs, indexes and other constraints outside the table definion proper.

Answer 6

I always structure my tables like this:

1. Primary key(s)
2. 'Tracking' columns (DateModified, ModifiedBy and such)
3. Any foreign keys
4. Data

Answer 7

I agree with most of the posts above, primary key first (at least). The rest is personal preference. If you have a standard then keep to that standard.

I do prefer to keep columns fairly logically together. Sometimes a fully normalised data structure is not appropriate so you have "minor entities" stored on the same table (i.e. not removing NULLs). An example would be the address fields, or the different telephone, mobile phone, work phone columns placed together.

The most striking example I can give is HOW NOT TO DO IT. If a developer autogenerates a schema and the columns are created in alphabetical order (and even the PK was hidden in the middle of the table structure) then that is MOST annoying.

Answer 8

As per edoode:-

Primary key(s) 
'Tracking' columns (DateModified, ModifiedBy and such) 
Any foreign keys 
Data

Plus

Fixed width 'not null' columns
Fixed width 'nullable' columns
Variable Width columns VARCHAR NVARCHAR etc.
CLOBS
BLOBS

You get a teeny weeny ever so slight performance increase if you stick columns that may vary in length at the bottom of the table.