Does the order of columns in a table's definition matter?

Question

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an element of good style.

That is clear.

What is not clear, however, is whether the logical ordering of columns in a table has any impact on their physical ordering at the storage layer, or if it has any other impact that one might care about.

Apart from the impact on style, does column order ever matter?

There is a question on Stack Overflow about this, but it lacks an authoritative answer.

Answer 1

Does the logical ordering of columns in a table have any impact on their physical order at the storage layer? Yes.

Whether it matters or not is a different issue that I can't answer (yet).

In a similar manner to that described in the frequently linked article from Paul Randal on the anatomy of a record, let's look at a simple two column table with DBCC IND:

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

USE master;
GO

IF DATABASEPROPERTY (N'RowStructure', 'Version') > 0 DROP DATABASE RowStructure;
GO

CREATE DATABASE RowStructure;
GO

USE RowStructure;
GO

CREATE TABLE FixedLengthOrder
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
    , c3 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL  
);
GO

INSERT FixedLengthOrder DEFAULT VALUES;
GO

DBCC IND ('RowStructure', 'FixedLengthOrder', 1);
GO

The output above shows that we need to look at page 89:

DBCC TRACEON (3604);
GO
DBCC PAGE ('RowStructure', 1, 89, 3);
GO

In the output from DBCC PAGE we see c1 stuffed with the character 'A' before c2's 'B':

Memory Dump @0x000000000D25A060

0000000000000000:   10001c00 01000000 41414141 41414141 †........AAAAAAAA
0000000000000010:   41414242 42424242 42424242 030000††††AABBBBBBBBBB...

And just because, lets bust open RowStructure.mdf with a hex editor and confirm the 'A' string preceeds the 'B' string:

Now repeat the test but reverse the order of the strings, placing the 'B' characters in c1 and the 'A' characters in c2:

CREATE TABLE FixedLengthOrder
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
    , c3 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL  
);
GO

This time our DBCC PAGE output is different and the 'B' string appears first:

Memory Dump @0x000000000FC2A060

0000000000000000:   10001c00 01000000 42424242 42424242 †........BBBBBBBB 
0000000000000010:   42424141 41414141 41414141 030000††††BBAAAAAAAAAA... 

Again, just for giggles, lets check the hex dump of the data file:

As Anatomy of a Record explains, the fixed and variable length columns of a record are stored in distinct blocks. Logically interleaving fixed and variable column types has no bearing on the physical record. However, within each block the order of your columns does map to the order of bytes in the data file.

CREATE TABLE FixedAndVariableColumns
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
    , c3 VARCHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL  
    , c4 CHAR(10) DEFAULT REPLICATE('C', 10) NOT NULL
    , c5 VARCHAR(10) DEFAULT REPLICATE('D', 10) NOT NULL
    , c6 CHAR(10) DEFAULT REPLICATE('E', 10) NOT NULL  
);
GO

Memory Dump @0x000000000E07C060

0000000000000000:   30002600 01000000 41414141 41414141 †0.&.....AAAAAAAA 
0000000000000010:   41414343 43434343 43434343 45454545 †AACCCCCCCCCCEEEE 
0000000000000020:   45454545 45450600 00020039 00430042 †EEEEEE.....9.C.B 
0000000000000030:   42424242 42424242 42444444 44444444 †BBBBBBBBBDDDDDDD 
0000000000000040:   444444†††††††††††††††††††††††††††††††DDD

See also:

Column order doesn’t matter… generally, but – IT DEPENDS!

Answer 2

If you do not define a clustered index, you'll get a heap table. For a heap table you'll always be scanning when reading data and thus the whole rows will be read, rendering the order of columns a moot point.

As soon as you define a clustered index, the data is physically rearranged to conform with the physical order of the columns as you specify - and at this point, the physical order becomes important. The physical order is what determines the seeking operator eligibility based on the predicates you're using.

While I can't remember reading it anywhere, I'd assume SQL Server does not guarantee the physical order of columns for heaps, whereas it will be guaranteed for indexes. To answer your question, no, the order of the columns in the definition should not matter as they won't matter when reading the data (note that this is only for heaps - indexes is a different matter).

Update
Actually you're asking two questions - "whether the logical ordering of columns in a table has any impact on their physical ordering at the storage layer" is a no. The logical order, as defined by the metadata, does not have to be in the same order as the physical one. What I gather you're looking for an answer to is whether the logical order in the CREATE TABLE results in the same physical order on creation - which I do not know, for heaps - though with the caveat above.

Answer 3

Based on what I have seen and read the ordering of columns in SQL Server makes no difference. The storage engine places columns on the row irrespective of how they are specified in the CREATE TABLE statement. That being said, I'm sure there are some very isolated edge cases where it does matter but I think you will have a hard time getting a single definitive answer on these. Paul Randal's "Inside The Storage Engine" blog category of posts is the best source for all the details on how the storage engine works that I am aware of. I think you would have to study all the various ways in which the storage works and matrix that against all of the use cases to find the edge cases where order would matter. Unless a specific edge case is pointed out that applies to my situation I just order the columns logically on my CREATE TABLE. I hope this helps.

Answer 4

I get what you mean. From design perspective a table that looks like this:

**EMPLOYEES**
EmployeeID
FirstName
LastName
Birthday
SSN 

is a lot better than a table that looks like this:

**EMPLOYEES**
LastName
EmployeeID
SSN 
Birthday
FirstName

But the Database engine doesn't really care about your logical column order if you issue a tsql like this:

SELECT FirstName, LastName, SSN FROM Employees

The engine just knows where the list of FirstName is stored in the disk.