The order of columns in a table [duplicate]

Question

From what I've seen so far, when we define a table, normally we put the primary key as the first column. If there are miscellaneous columns that are common in all tables, they come at the bottom of the table definition. For example: Id (the pk) the first column and IsActive and timestamp at the bottom.

I am wondering if this practice has got more benefits other than only consistency across the database. Does the order of the columns affect the performance? How? I need to justify my opinion in the team that placing the primary keys and the common keys such as IsActive (Boolean column shows whether the record is active or not), randomly in the tables is not a good practice!

The developers create the entities in entity model in the Visual Studio 2010 and if they add a new attribute to an existing entity apparently VS does not put them in a right order. That's their excuse to overlook the order of the columns in a table. Does it affect the performance?

I appreciate your thoughts.

Answer 1

The on-disk structure of a table is independent of the order of columns the CREATE TABLE. This means that performance is not affected.

Each entry (this applies to both data and index) looks like

• Header
• Fixed length columns
• Null bitmap
• Variable length columns
• Row version

For more, see Inside the Storage Engine: Anatomy of a record

There are a few cases where it can matter. But you won't usually find them day to day: normaly in repro scripts you'll find here or in blogs. For example, see comments to my SO answer here: https://stackoverflow.com/a/6121952/27535 and the link in comments below by @SQL Kiwi

However, from a consistency, maintainability and readability, I'd have PK columns first with columns in the same order as the actual PK key columns. Saying that, I don't normally rebuild tables to ensure that columns are in the "correct" place. It really does make little difference except to my OCD.

Answer 2

If you are adding columns to an existing table then the developers are correct to say that you cannot change the order of the columns (well unless you want to drop and recreate the table anyway).

In regards to what order to place the primary key - I guess it's down to preference. Our company standard is to put a time stamp as the first column and the primary key ID as the second.

As to whether the order of columns have an impact on the speed, well if we think about it then probably yes but this is likely to be miniscule. Think about how the data is organised in the page: the data for later columns comes last however, it is all based in offsets and pointers so it gets a bit complicated.

At the end of the day the main recommendation would be consistency, if your primary key comes first in one table it would be helpful to do the same in other tables - but it is not required.

Also remember that the primary key is indexed anyway so what Number column it is will matter very little.

I hope this helps you.

Answer 3

You may be interested in the following:

• http://www.sqlservercentral.com/Forums/Topic558567-149-1.aspx#bm558607

• https://stackoverflow.com/questions/1610/can-i-logically-reorder-columns-in-a-table

• https://stackoverflow.com/questions/4402312/why-cant-i-reorder-my-sql-server-columns

All in all, you can only add columns after the existing ones. If you want to reorder columns, you have to drop and recreate your table - but with enough dependencies this can be very painful.

I create new tables along the lines you've described, but if I have to add new fields, I don't care too much and just append them - both in the model and in the database itself.

And see gbn's answer too.

Answer 4

In my opinion it is good practice since it is a lot easier to read a table and understand it's contents and purpose if you don't have to burrow into it just to find its primary key.

I haven't found anything against it in a technical sense.