Performance / Space implications when ordering SQL Server columns?

Question

Are there any considerations that should be taken into account when designing a new table with regards to the order in which columns should be declared? I tend to put the primary key first, followed by any foreign keys (usually surrogate key integers), followed by other columns, but a discussion with a colleague had us wondering whether SQL Server will pad our data, possibly to make it faster.

Will SQL Server try and align our data on disk (with padding) to a specific byte alignment boundary for performance reasons (the way a C++ compiler would align a struct under default conditions) or will it just allocate as many bytes as our total row requires (possibly padding at a row level)? I.e. if we have a 3 byte char column and another column of type bit / tinyint, could we expect any change in behaviour (better or worse) from the server by making one of these follow another to align at 4 byte boundaries? Does SQL Server even care what order I declare the columns, or is it free to lay them out as it sees fit?

I understand that there are probably a million things I should look to first before trying to optimise the layout of my table columns, but for curiosity's sake I'm interested to know whether SQL Server cares about column ordering at all, and if so, where one would go to (DMV, etc??) see how it's physically laying the rows out on disk.

Answer 1

SQL Server stores the data on disk in set and fixed fashion.

The order in sys.columns and key columns has no relevance to this on-disk order.

See "Anatomy of a record" (Paul Randal) and my answer here: How do you get to limits of 8060 bytes per row and 8000 per (varchar, nvarchar) value?