Data Compression in SQL Server

Data compression is another feature for SQL Server which is used to compress data tables, indexes, or partitions within a database and shrink the amount of space used to store the data. Compressing data means to reduce database storage, which improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. However, SQL Server consumes more CPU resources when it compresses and decompresses data.

SQL Server supports row and page compression for tables and indexes. With the help of this technology, we can compress the data inside a database to reduce the size of the database. SQL Server stated that data compression can be configured for the following database objects:

1. A whole table that is stored as a heap.
2. A whole table that is stored as a clustered index.
3. A whole non-clustered index.
4. A whole indexed view.
5. For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.

For ColumnStore tables and indexes, all ColumnStore tables and indexes always use ColumnStore compression and this is not user configurable. Use ColumnStore archival compression to further reduce the data size for situations when you can afford extra time and CPU resources to store and retrieve the data. We can configure ColumnStore archival compression on the following database objects:

1. A whole ColumnStore table or a whole clustered ColumnStore index. Since a ColumnStore table is stored as a clustered ColumnStore index, both approaches have the same results.
2. A whole nonclustered ColumnStore index.
3. For partitioned columnstore tables and columnstore indexes, we can configure the archival compression option for each partition, and the various partitions do not have to have the same archival compression setting.

Discomforts with Data Compression 
Data compression is currently supported in the Enterprise, Developer, and Evaluation editions of SQL Server. Both row and page compression will consume additional space when they're implemented which is known as metadata overhead. In this case, when the metadata overhead is greater than the space saved, implementing data compression will actually expand the storage space.
To know more on Data Compression-
Considerations for When You Use Row and Page Compression
ColumnStore Archive Compression in SQL Server
How does Row Compression work?
What is Data Compression in SQL Server?

What is Page Compression in SQL Server?
References:https://msdn.microsoft.com/en-us/library/cc280449(v=sql.120).aspx