Using Data Compression

Using Data Compression

         Data compression has the drawback of increasing CPU usage because the data must be compressed and decompressed when being accessed. You cannot use data compression with system tables, and only the Enterprise and Developer editions of SQL Server 2012 support data compression.

You can configure data compression on the following:

■■ Clustered tables

■■ Heap tables (a heap is a table without a clustered index)

■■ Non-clustered indexes

■■ Indexed views

■■ Individual partitions of a partitioned table or index

THREE FORMS OF DATA COMPRESSION

1.Row-level compression.

2.Unicode compression.

3.Page-level compression.

Row-Level CompressionRow-level compression works by using more efficient storage formats for fixed-length data.Row-level compression uses the following strategies to save space:
■■ Storing fixed-length numeric data types and CHAR data types as though they were
variable-length data types■■ Not storing NULL or 0 values
■■ Reducing metadata required to store data
 You use the following syntax to compress a table by using row-level compression:
ALTER TABLE table Name REBUILD WITH (DATA_COMPRESSION=ROW)  You use the following syntax to configure an index with row-level compression:
ALTER INDEX index Name ON table Name REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

Unicode Compression

        Unicode compression enables the database engine to compress unicode values stored in

page or row compressed objects. You can use unicode compression with the fixed-length

nchar(n) and nvarchar(n) data types. Unicode compression is automatically used where appropriate when you enable row and page compression.

 Page-Level Compression

         Page-level compression compresses data by storing repeating values and common prefixes

only once and then making references to those values from other locations within the table.

When page compression is applied to a table, row compression techniques are also applied.

Page-level compression uses the following strategies:

■■ Row-level compression is applied to maximize the number of rows stored on a page.

■■ Column prefix compression is applied by replacing repeating data patterns with references.

This data is stored in the page header.

■■ Dictionary compression scans for repeating values and then stores this information in

the page header.

  You use the following general syntax to apply page-level compression:

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE

     You use the following syntax to configure an index with page-level compression:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

Estimating Compression

The best way to determine the benefits of compression on an object is to use the

sp_estimate_data_compression_savings stored procedure. The benefits of compression

depend on factors such as the uniqueness of data. The sp_estimate_data_compression_savings

stored procedure is available in the Enterprise edition of SQL Server 2012 only.

The syntax of the stored procedure is as follows:

sp_estimate_data_compression_savings[ @schema_name = ] 'schema_name', [ @object_name = ]

'object_name', [@index_id = ] index_id,[

To configure an estimate of the compression benefits of using Page compression on the

same table, execute the following Transact-SQL statement:

USE AdventureWorks2012;

GO

EXEC sp_estimate_data_compression_savings 'HumanResources', 'Employee', NULL, NULL,

'PAGE';

GO