Brent Ozar has a new animation:
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
Here’s my dirty little secret: I don’t think row-level compression makes sense all that often, simply because I’m not sure I’ve ever seen negative consequences to page level compression, even in a variety of scenarios in very busy environments. I’m sure that there are specific cases, but I just default to page level compression because of how well it works.