Andy Mallory continues his discussion of compression options:

You can think of page compression as doing data deduplication within a page. If there is some value repeated in multiple spots on a page, then page compression can store the repetitive value only once, and save some space.

Page compression is actually a process that combines three different compression algorithms into a bigger algorithm. Page compression applies these three algorithms in order:

1) Row compression

2) Prefix compression

3) Dictionary compression

Page compression is my go-to compression option, typically. There are some cases in which it doesn’t work well, so check beforehand (start with sp_estimate_data_compression_savings), but I’ve had good luck with page compression.

Comments are closed

sp_estimate_data_compression_savings is a great tool–I’ll tackle it in the near future in another post. Since it relies on sampling, it’s not perfect.

Just like statistics, if you get a sample that doesn’t represent the entire table, the estimate could be off.

Trust, but verify!