OK, for years I've been saying that SQL Server doesn't care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last. In both the fixed-width portion of the row as well as the variable-width portion of the row, the columns are defined in the order in which they are declared. So, what does matter?

It's all in the cost of the variable array's offset values. If the large majority of your NULLable records are at the end, then SQL Server doesn't need to completely populate the variable block array (which saves 2 bytes per column). If you have a table where 36 columns are NULLable and generally they are NULL, then defining those columns at the end of the row can save you space.

The following script will show you how the maximum length of the row changes based on whether or not a later column in the variable block is NOT NULL – even when most/all of the prior columns are!

CREATE TABLE RowSizeVariableBlock
(
ID int NOT NULL identity,
c01 char(10) NOT NULL default 'test',
c02 datetime2(7) NOT NULL default sysdatetime(),
c03 char(80) NOT NULL default 'junk',
c04 varchar(100) NULL,
c05 varchar(100) NULL,
c06 varchar(100) NULL,
c07 varchar(100) NULL,
c08 varchar(100) NULL,
c09 varchar(100) NULL,
c10 varchar(100) NULL,
c11 varchar(100) NULL,
c12 varchar(100) NULL,
c13 varchar(100) NULL,
c14 varchar(100) NULL,
c15 varchar(100) NULL,
c16 varchar(100) NULL,
c17 varchar(100) NULL,
c18 varchar(100) NULL,
c19 varchar(100) NULL,
c20 varchar(100) NULL,
c21 varchar(100) NULL,
c22 varchar(100) NULL,
c23 varchar(100) NULL,
c24 varchar(100) NULL,
c25 varchar(100) NULL,
c26 varchar(100) NULL,
c27 varchar(100) NULL,
c28 varchar(100) NULL,
c29 varchar(100) NULL,
c30 varchar(100) NULL,
c31 varchar(100) NULL,
c32 varchar(100) NULL,
c33 varchar(100) NULL,
c34 varchar(100) NULL,
c35 varchar(100) NULL,
c36 varchar(100) NULL,
c37 varchar(100) NULL,
c38 varchar(100) NULL,
c39 varchar(100) NULL,
c40 varchar(100) NULL
)
go

insert RowSizeVariableBlock DEFAULT VALUES
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
– review "max" record size = 114
go

insert RowSizeVariableBlock (c01, c03, c20)
values ('med row', 'up to c20', 'test')
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
– review "max" record size = 154
go

insert RowSizeVariableBlock (c01, c03, c30)
values ('med+ row', 'up to c30', 'test')
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
– review "max" record size = 174
go

insert RowSizeVariableBlock (c01, c03, c40)
values ('large row', 'up to c40', 'test')
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
– review "max" record size = 194
go

While there are some other optimizations at this level, most tables cannot benefit from this as the data populations aren't as predictable nor are most tables filled with so many variable-width and NULLable columns. However, if you do have this data pattern, defining these columns at the end of your table's definition – MIGHT save a tremendous amount of space, especially when this table is very large!

Paul's blogged more on these structures as well as the NULL bitmap here: http://www.sqlskills.com/blogs/PAUL/post/Misconceptions-around-null-bitmap-size.aspx.

Enjoy! And, thanks for reading,
kt