Table compression, Row or Page?

Question

Need to compress a giant table of 1.5TB in sql server 2008, which is the best option Page or row compression, please let me know the steps to do it.

Answer 1

There is no such a thing as "best option". If page would always be superior, why bother offering row as sub-par alternative or vice versa? It really depends on a lot of things, so further analysis is needed. Start by reading some materials about data compression. MS has published a technical article Data Compression: Strategy, Capacity Planning and Best Practices that's a bit old, but fits your Sql Server version.

By the way, Sql Server 2008 has been out of mainstream support since Q2 2014 (and this is assuming you are running the latest SP4 patchlevel). You really should consider upgrading into more recent a version of Sql Server.

Answer 2

First of all I would suggest to partition the table according to dependent queries if it's not partitioned and index the table accordingly (better to include partition base column in all indexes). Because querying such big table would cause to slow performance of the query and it will consume more CPU and memory as well.

Compression

Row level : compression does not compress instead it allows the usage of only the actual amount of storage required. Less compression achieved by this but less CPU overhead as well.

Page Level : compression eliminate duplicate value and keep only one. when we enable page level compression, row level compression is automatically enabled. More CPU overhead.

Though Page Level compression is superior (save more space), compression depends on the type of data you stored in the table, data duplication and it's HEAP or non HEAP. Non HEAP table gives better compression.

A table has three types of allocation unit IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA. The data stored in LOB_DATA and ROW_OVERFLOW_DATA does not get compressed.

I would suggest to go through https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx and analyze you environment accordingly before go ahead.

Thanks