Sql-server – Does the order of columns in a table’s definition matter

database-designdatabase-internalssql serversql-server-2008

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.

Best Answer

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

DBCC IND output

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:

AAAAAAAAAA

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:

BBBBBBBBBB

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!

Related Question