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.
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!