Database Animations: The Difference Between Row Compression and Page Compression

What’s the difference between SQL Server’s row compression and page compression, and when does each one make sense?

• Row compression turns every fixed-length datatype into a variable-length datatype, using as little space as possible to store it
• Page compression does that, AND adds a dictionary of repeated data on the page, getting more compression at the cost of more CPU

To see it in more detail, let’s fire up one of my Database Animations.

How Row Compression Works

Let’s start by looking at the clustered index of the Users table from the Stack Overflow database you know and love so well. That table some fixed-length columns like:

• Id: INT, 4 bytes
• Reputation : INT, 4 bytes
• Age: INT, 4 bytes
• CreationDate: DATETIME, 8 bytes

However, a lot of the rows don’t need the full 4 or 8 bytes in order to store their data. Most peoples’ reputations (especially yours) is a very small number, so it doesn’t need all 4 bytes of storage. Age used to be populated, but these days it’s null for security purposes, so nothing goes in there at all. If we enable row compression (by rebuilding the clustered index with row compression turned on), let’s see how storage is impacted:

With row compression, SQL Server automatically uses the smallest amount of space possible to store the relevant data. The bigger of a fixed-length datatype your datamodel uses, and the smaller your data actually is, the bigger your compression savings will be. I kinda think of row compression as undoing the technical debt cost of people who said, “HEY LET’S USE BIG DATATYPES JUST IN CASE!”

For the most part, row compression’s savings only impacts fixed-length datatypes. If you’re using VARCHAR(4000) in order to store postal code, you’re not going to see any savings here, because SQL Server already just stores the length of the data, not all 4,000 bytes by default. Let’s be honest – this is where those big-data-datatypes people really went wild and crazy, and their overzealousness at picking big numbers never really hurt you in terms of storage taken up anyway.

There’s one edge case where row compression savings actually helps variable-length datatypes: when you store VARCHAR values in NVARCHAR datatypes. For more on that edge case, check out Which Should You Use: VARCHAR or NVARCHAR? I hate to give away spoilers, but I feel like I need to here because most experienced architects make the wrong choice: you should be using NVARCHAR, even if you’re storing VARCHAR data, and enabling row compression.

How Page Compression Works

Row compression works at the row level. It’s zoomed in to just what’s happening on a single row. At that level, you can’t really identify duplication of data because we rarely store the same data over and over again in different columns.

Page compression works at the page level, though, and it does identify duplicate data stored over and over again on the same page. It builds a dictionary on that page, storing redundant data just once.

Continuing with our Users clustered index example, let’s think about the Location column stored on that page. (To keep things simple for the diagram, I’m only showing the Location column’s contents, but of course all of the columns are stored on these 8KB pages.) Odds are, we’re going to have some people with the same (or at least similar) locations on any given 8KB page, and that’s where de-duplicating data starts to save space:

This technique saves more space, even on variable-length columns, by identifying those duplicates on each page, and only storing them once. This does come with the overhead of more CPU consumed to analyze each row on the page in relation to the other rows, identifying those duplicates, building the dictionary, and updating the stored values.

This is especially powerful on nonclustered indexes. Eagle-eyed readers will look at the above animation and say, “Wait, on the clustered index, it’s fairly unlikely that I have several rows with the same location. The clustered index is sorted by Id, not by Location, and it’s extremely unlikely that a bunch of people from the same location are gonna sign up at the same time, in order.” Those are my favorite kinds of readers because they put their hands up in class, and I get to say “Good question!” and I get genuinely excited, because they’re absorbing the information. I know, you didn’t catch that, and that’s okay – you’re my second favorite kind of reader. Anyhoo, on nonclustered indexes, especially on an index like Location, we’ll see a ton of duplicate values stored in order on the same 8KB page, and page compression savings is huge here.

There are a couple of gotchas with page-level compression. First off – and I hate to beat this dead horse – but it only works at the 8KB page level. If you store your large strings off-row, page compression doesn’t work there. This especially hits big string data like JSON and XML – very wordy data with a lot of duplicate strings, exactly where we would want compression, but page compression just doesn’t do anything about that.

Second, the dictionary is still only stored at the page level. In a perfect world, we’d have a dictionary across an entire table’s contents, at the column level, so that we only had to store the term “Helsinki” once, and refer to it everywhere with the same dictionary reference number, no matter which 8KB page the person’s location was stored on. That doesn’t happen, though, because page compression works at the page level – not at the column level or the table level.

Finally, your worst case scenario for page compression would be data that is frequently updated in-place (on the same 8KB page), that isn’t compressible. SQL Server will waste CPU cycles checking to see if any of the data can be compressed, and coming up empty each time.

So, Which Should You Use?

Use row compression if:

• You followed my advice about storing string data in NVARCHAR columns, and/or
• Your architects used big variable-length datatypes to store tiny data

Use page compression if:

• Your 8KB pages have a lot of redundant stuff on-row, on the same 8KB pages (which for nonclustered string indexes, is all of them), and
• You’re bottlenecked on reading data pages from disk (PAGEIOLATCH), not on CPU (SOS_SCHEDULER_YIELD)

To enable either form of compression, simply rebuild your indexes:

Note: I generated the animations in this post with Claude Code, but all of the text & demos are completely written by me.