Please explain the difference between row and page compression

Question

I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community. I have read several articles, but wanted to have a place where this addressed in DB Administrators.

Answer 1

This is a good reference and explanation for row v page (no pun intended) compression: http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/how-sql-server-data-compression/

In short though, page compression encompasses the algorithms contained with row level compression and then also covers Prefix compression and Dictionary compression. Prefix and dictionary compression finds patterns in the data and replaces them with smaller values. During decompression it will do the reverse and restore the data to its original values.

Row level makes easier compression decisions, like reducing metadata, and removing unused space, like the letter 'Y' stored in a CHAR(1000).

The article referenced explains this in much greater detail.

Answer 2

In order to make a decision for bunch of tables you can run procedure "sp_estimate_data_compression_savings":

exec sys.sp_estimate_data_compression_savings  @Schema,@Table,NULL,NULL,ROW;
exec sys.sp_estimate_data_compression_savings  @Schema,@Table,NULL,NULL,PAGE;

That will help you determine savings for each particular table.

From my experience PAGE compression works better in most of the cases.