Microsoft SQL Server 2008 introduces two new compression features (available in Enterprise and Developer editions) that are of interest to users with large databases. Data compression applies to individual tables and indexes within a database and comes in two options: ROW and PAGE. Backup compression is a new option when backing up a database to external storage. Both compression techniques can be used to reduce the amount of online storage required for databases and their backups.

Data Compression
Data compression can be configured for an entire heap (table with no clustered index), clustered index, non-clustered index, or indexed view. For partitioned tables and indexes, the compression option can be configured separately (and differently) for each partition.

The data compression option can be specified during CREATE TABLE or CREATE INDEX by using the new table_option DATA_COMPRESSION = NONE or ROW or PAGE. The data compression option can also be changed later through ALTER TABLE or ALTER INDEX with a (new for TABLE) REBUILD clause. Data compression affects the physical storage of columns within a row and rows within a page on disk and in memory. It does not change the logical attributes of the data or the way it is presented by the database, so there are no changes visible to the application.

Data compression requires more processing for select, insert, and update than for uncompressed data.

Furthermore, compression is generally more expensive than decompression. For these reasons, in most cases, Microsoft recommends that compression not be used on tables and indexes if the size of the object is much smaller than the overall database or if the table is heavily used for DML operations. Note that there is no database-wide compression option.

Compressing large tables and indexes that generate significant I/O volume can frequently improve their memory caching and reduce the I/O volume enough to compensate for (much of) the compression/decompression overhead, thus reducing storage costs without undue change in performance. In certain I/O-bound (or memory constrained) situations, data compression can even improve overall performance. To estimate how changing the compression state will affect the amount of storage required for a table or index, use the sp_estimate_data_compression_savings stored procedure or the Data Compression Wizard.

Download pdf Microsoft SQL Server 2008 Data and Backup Compression