SQL Server: Off-Row Storage
NVARCHAR(MAX) on LOB pages — keeping the fat out of the scan
SELECT
Id, DisplayName
FROM
dbo.Users;
-- no AboutMe
SELECT
*
FROM
dbo.Users;
-- * includes AboutMe
AboutMe in-row · 12 leaf pages
1:701
1:702
1:703
1:704
1:705
1:706
1:707
1:708
1:709
1:710
1:711
1:712
2 fat rows per page — AboutMe inside every row
scan reads:
—
12 (every fat page)
AboutMe off-row · 3 leaf + 8 LOB pages
1:501
1:502
1:503
a 16-byte pointer, not the value
LOB pages · the AboutMe values
…never read — nobody asked for AboutMe
scan reads:
—
3 (thin leaf pages only)
3 + 8 LOB = 11
scan: 12 vs 3 — the fat stayed home