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