Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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.

share|improve this question

marked as duplicate by Nick Chammas, Mark Storey-Smith, dezso, Mat, Max Vernon Aug 29 '13 at 13:06

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
I know this is late, but have you seen this question about column order? –  Nick Chammas Aug 29 '13 at 6:35
add comment

4 Answers 4

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: http://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.

share|improve this answer
4  
+1 Also adding one of the 'few cases': sqlskills.com/BLOGS/KIMBERLY/post/… –  Paul White Sep 5 '12 at 9:56
1  
And barring some really unusual cases, you shouldn't actually care - SQL (and DBMSs) were designed to abstract this type of stuff away. It lets them do stuff like combine bit fields into byte storage, without the user having to worry about it. –  Clockwork-Muse Sep 5 '12 at 16:40
add comment

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.

share|improve this answer
add comment

You may be interested in the following:

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.