We've finally booked all of our tickets and started planning final details for all our of our classes and conferences this fall. We've got quite a few things planned and tons of great content at each. By continent, here's the plan:

Europe

• Dublin, Ireland: September 21-25. Paul and I will be teaching a week-long Immersion Event - combining internals, performance tuning, database maintenance and more.
• Warsaw, Poland: September 29-30. Paul and I will be presenting a full day of SQL Server sessions at the Microsoft Technology Summit 2009

Australia - Melbourne

• October 15-16. Paul and I will be teaching a 2-day class "SQL Server 2008: New Features - Updating Your Administrations Skills in Database Infrastructure and Scalability"
• October 19-22. Paul will be teaching a 4-day class "SQL Server 2005/2008: DB Maintenance and Availability: From Performance to Disaster Recovery"
• October 19-22. I will be teaching a 4-day class "SQL Server 2005/2008: Performance Tuning – From Design to Indexing to Optimizing Procedural Code"

US

• Seattle, WA for SQLPASS: November 2-5. Paul and I will be teaching two full-day workshops and a Spotlight Session each.
• Las Vegas, NV for SQL Connections: Nobember 9-12: Paul and I will be teaching two full-day workshops and 5 sessions. Stacia will be teaching 4 sessions on BI.
• Redmond, WA for SQL MCMs and Sharepoint MCM's through the end of this year

You can find more details and links to the various registration sites on our Upcoming Events page. And, if you have any questions, feel free to email me: Kimberly AT SQLskills.com or Paul AT SQLskills.com.

We hope to see you there and be sure to swing by and say hi!

Cheers,
kt

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