SQL Server: Row Compression
Shrink the containers to fit the values
ALTER TABLE
dbo.Users
REBUILD
WITH
(DATA_COMPRESSION =
ROW
);
One row of dbo.Users — magnified
dark = bytes the value needs · light = declared but unused
Id · INT
value 12,345
4 B
4 B → 2 B
Reputation · INT
value 87
4 B
4 B → 1 B
Age · INT
value NULL
4 B
4 B → 0 B
CreationDate
DATETIME · 2010-03-14
8 B
8 B → 4 B
DisplayName
NVARCHAR · N'Brent'
10 B
already variable — untouched
row size: 20 B fixed + 10 B variable = 30 B
row size: 2 + 1 + 0 + 4 + 10 B ≈ 17 B
cost: a little CPU on every read & write
Leaf page · clustered index
Page 1:301
rows on this page: 6
rows on this page: 6 → 10
The whole table
pages: 5
pages: 5 → 3