Performance / space impact when ordering SQL Server columns?

Question

Are there any considerations to consider when designing a new table regarding the order in which columns should be declared? I first set the primary key, followed by any foreign keys (usually surrogate key integers), and then the other columns, but a discussion with a colleague made us wonder if SQL Server would populate our data, perhaps to make it faster.

Would SQL Server try and align our data on disk (with the addition) to a certain byte alignment boundary for performance reasons (the way the C ++ compiler aligned the structure in default conditions) or would it just allocate as many bytes as our shared does a row require (perhaps row-level padding)? That is, if we have a 3-byte char column and another bit / tinyint column, can we expect any changes in the behavior (better or worse) from the server, making one of them next to the other in order to align by 4 bytes ? Does SQL Server even care about what order I declare in the columns, or is it free to lay them out as I see fit?

I understand that there are probably a million things that I should first look at before trying to optimize the layout of my table columns, but for the sake of curiosity, I’m interested in knowing if SQL Server cares about ordering the columns in general, and if so, where could to go (DMV, etc.), see how it physically puts lines on disk.

Answer 1

SQL Server stores data on disk in an installed and fixed form.

The order in the sys.columns and key columns is not relevant for this order on disk.

See “Recording Anatomy” (Paul Randal) and my answer here: How do you get within the limits of 8060 bytes per line and 8000 on the (varchar, nvarchar) value?