SQL Server 2008 introduced a feature called Data Compression, which allows you to reduce the size of tables, indexes, or a subset of their partitions. This not only helps in reducing storage costs but also improves query performance by reducing I/O and increasing buffer-hit rates.

Data Compression in SQL Server has been available since SQL Server 2005 with Service Pack 2, where a new storage format called vardecimal was introduced for storing decimal and numeric data. In SQL Server 2008, this concept was extended to all fixed-length data types such as integer, char, and float data types.

There are two types of data compression in SQL Server 2008: ROW Compression and PAGE Compression.

ROW Compression

ROW Compression takes into account the variable data type structures defining a column. For example, a CHAR(100) column stored in a variable length storage format will only use up the amount of storage defined by the data. Storing “SQL Server 2008” in the column will only require storing fifteen characters instead of the full 100 characters, resulting in an 85% savings on storage space. ROW Compression also does not take any disk space for zero or null values.

PAGE Compression

PAGE Compression is a superset of ROW Compression and takes into account redundant data in one or more rows on a given page. It uses prefix and dictionary compression techniques to reduce repeated data on the page. For example, if a table is partitioned using a column prefix, all data in a specific partition will have the same or similar prefix. The storage engine stores the prefix value once on the page and then refers to this value from all other occurrences of the same value on the same page. PAGE Compression only occurs when the page is full to optimize performance.

Before enabling data compression on your tables or indexes, it is recommended to evaluate the estimated space savings. You can use the sp_estimate_data_compression_savings system stored procedure or the Data Compression Wizard to estimate the storage space savings.

Estimating Storage Space Savings

To use the sp_estimate_data_compression_savings system stored procedure, you can execute the following query:

USE AdventureWorks;
EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL, 'ROW';

This query will provide you with an estimate of the space savings you will achieve in the Sales.SalesOrderDetail table using ROW compression.

To use the Data Compression Wizard, right-click on the Sales.SalesOrderDetail table, select Storage, and click Manage Compression. This will launch the Data Compression Wizard, where you can select the compression type and calculate the space savings.

Enabling Compression on a Table

To enable compression on an existing non-partitioned table, you can use the ALTER TABLE command with the REBUILD option:

ALTER TABLE Sales.SalesOrderDetail REBUILD WITH (DATA_COMPRESSION = ROW);

This command enables ROW compression on the Sales.SalesOrderDetail table.

It is recommended to include data compression as part of your regular database maintenance. You can create a script using the Data Compression Wizard and include it in a database maintenance job.

By utilizing data compression in SQL Server, you can significantly reduce storage costs and improve query performance. It is important to evaluate the estimated space savings and choose the appropriate compression type for your tables and indexes.