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.

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?

share|improve this question
3  
There is a question on SO about this, but it lacks an authoritative answer. –  Nick Chammas Jun 1 '12 at 22:40
    
is there any mysql or postgres software to easily re-arrange columns for performance / size minimization? –  Neil McGuigan Jun 13 '12 at 19:48
    
@Neil - Dunno. Sounds like a question you could ask on the site, though from Mark's answer below (and Martin's comment on it) I doubt you will get any big performance boost from just rearranging columns. –  Nick Chammas Jun 13 '12 at 19:58
    
It would be interesting to know, for example, in a table with three INTs and two BITs, if the column order would influence the size of the row (and how many of those rows would fit in a page). If the columns are byte-aligned physically, then I would think that putting the INTs together and the BITs together (Int1, Int2, Int3, Bit1, Bit2) would take up fewer bytes than if you alternate them (Int1, Bit1, Int2, Bit2, Int3) because the BITs might consume a byte each if they are in between INTs, whereas the two BITs might only consume one byte combined if they are contiguous. –  Mark Aug 20 '13 at 19:18
add comment

4 Answers 4

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, lets 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
share|improve this answer
    
+1 I agree. I've always found that within each section the order of the columns initially is as per the CREATE TABLE statement (except that CI key columns do come first in the section). Though the order of the columns can change if ALTER COLUMN changes datatypes/column lengths. The only minor case where it matters that I can think of is that columns at the end of the variable length section with empty string or NULL take no space at all in the column offset array (demonstrated by Kalen Delaney in the 2008 internals book) –  Martin Smith Jun 2 '12 at 8:33
    
Also see sqlskills.com/BLOGS/KIMBERLY/post/… –  Paul White Jun 3 '12 at 10:08
    
@Martin and Mark - Can you imagine any other scenarios where column order would have a notable impact on the behavior of the database? If not, it seems like it's safe to say that, as far as performance is concerned, column order in a table's definition does not matter. Do you agree? –  Nick Chammas Jun 7 '12 at 18:38
    
@NickChammas Possibly/maybe but it would be very marginal... working on it but I haven't had a chance to script up an example yet. –  Mark Storey-Smith Jun 7 '12 at 21:17
add comment

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.

share|improve this answer
    
Indeed, I'm asking two related questions. The first is "Does logical order impact physical order?" and the second is "Does logical order matter?" "Matter" is up to your own interpretation. Does it affect the table in any way you might care about? –  Nick Chammas Oct 11 '13 at 22:14
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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