What ROW and PAGE Compression Do to the Record

Data compression is one of the few storage features you can turn on with a single clause and measure the same afternoon. It changes the physical format of the record on the page, not the meaning of your data, so no query has to change.^[1] This post takes one table, stores identical rows in it three ways, uncompressed, row-compressed, and page-compressed, and reads the records off the page to see exactly what shrank and how.

This continues the byte-level storage series alongside decimal, the NULL bitmap, and row-overflow and LOB. If DBCC PAGE is new to you, this older post covers the mechanics. Every number below is from a SQL Server 2019 instance, and the script is included so you can reproduce it. Row and page compression have been in SQL Server since 2008, and available in every edition since SQL Server 2016 Service Pack 1, a point that matters for the edition-downgrade question.^[2]

The setup: the same rows, three ways

One narrow table with a deliberately wasteful shape, an int, a bigint holding a tiny value, and a char(20), created three times with different DATA_COMPRESSION settings and loaded with 500 identical rows:

sys.dm_db_index_physical_stats reports the average record size for each:

Uncompressed, each record is 39 bytes. Row compression nearly halves that to about 21. Let us read the actual bytes to see where the difference comes from.

Uncompressed: fixed width, whatever the value

The uncompressed record:

Add it up. Four bytes of header, then the int id (bd 00 00 00), then the bigint n: 07 00 00 00 00 00 00 00. That is the value 7 taking all eight bytes of a bigint, seven of them zero. Then the char(20) holding SAMEVALUE padded with blanks to the full 20 bytes, then the column count and NULL bitmap. Thirty-nine bytes, most of them storing nothing: leading zeros in the bigint and trailing blanks in the char.

Row compression: store the value, not the width

Row compression targets exactly that waste. It uses a variable-length format for numeric types, so a bigint holding 7 no longer costs eight bytes, and it stores fixed-length character data without the trailing blanks.^[1] The record changes shape entirely:

It is now a (COMPRESSED) PRIMARY_RECORD, and instead of a fixed region plus a NULL bitmap it carries a CD array, a column descriptor array with a small code per column describing how many bytes that column actually uses. A code of TWO_BYTE_SHORT means the column is stored in two bytes rather than its declared width. The tiny bigint and the blank-trimmed char both collapse, and the record drops from 39 bytes to 21. The metadata format changed, but SELECT n FROM ... still returns 7; nothing above the storage engine can tell.

Page compression: row compression plus sharing

Page compression is a superset. It first applies row compression to every record, then looks across the whole page for redundancy and removes it two ways: a column prefix that factors out common leading bytes within a column, and a page dictionary that stores a repeated value once and points every occurrence at it.^[1] With 500 identical rows, the per-record format looks almost like the row-compressed one:

The real page-level win is not visible in a single record; it is that the repeated SAMEVALUE string and the repeated 7 are stored once per page in the compression information structure and referenced by every row. On highly repetitive data that is a large saving; on data with few repeats, page compression can add overhead for little gain, which is why it is worth measuring per table rather than applying everywhere.

What this means in practice

• Compression trades CPU for I/O. Fewer pages mean fewer reads and more rows cached in the same memory, at the cost of CPU to pack and unpack records. It shines on I/O-bound workloads and large, cool data; it can hurt a CPU-bound OLTP hot path.
• Row compression is the safe default to consider first. It reliably reclaims the wasted width of over-sized numeric and fixed character columns with modest CPU, and it rarely makes anything worse.
• Page compression rewards repetition. Its prefix and dictionary need repeated values to work against. Measure with sp_estimate_data_compression_savings before committing, especially on unique or high-entropy columns.
• It is available everywhere now. Row and page compression shipped in SQL Server 2008 as an Enterprise-only feature; since SQL Server 2016 SP1 they are in every edition including Standard and Express, so it is no longer an Enterprise-only lever.

Compression is not magic; it is the storage engine declining to write the zeros in your bigint and the blanks in your char, and on a page of repeats, declining to write the same value twice. Seeing the CD array in place of the fixed region makes it obvious that nothing about your data changed, only the shape it takes on disk.

Have you turned on row or page compression and watched the page count fall, or found a table where it did not pay off? I would like to hear it. Find me on Bluesky or LinkedIn.

References

1. Data compression and Row compression implementation – Microsoft Learn. Compression changes only the physical storage format; row compression uses variable-length storage for numeric types and drops trailing blanks from fixed strings; page compression adds column-prefix and page-dictionary sharing on top of row compression. ↩
2. Data Compression for SQL Server 2008 – Microsoft SQL Server Blog. SQL Server 2008 Enterprise Edition introduced native ROW and PAGE data compression; the six formerly Enterprise-only programmability features, compression among them, became available in all editions in SQL Server 2016 Service Pack 1. ↩