Database Management

SQL Server 2008 R2 compression features can help organizations manage ever-increasing amounts of data, control costs, and improve application performance.

Save storage space

• Store more data per gigabyte of space.

• Use disks and other storage media more efficiently.

• Manage large volumes of data more easily.

• Save data compression space—some customers report more than 80 percent improvement.

• Save space on backup compression—some customers report up to 66 percent savings.

• Use the stored procedure sp_estimate_data_compression_savings to provide an estimate of the likely savings.

Improve performance

• Use less space to store the same number of data rows.

• Fit more data in the SQL Server memory cache.

• Compress tables, partitions, and indexes, including indexed views.

• Achieve faster response times for users, particularly with input/output–bound queries.

• Improve response time—some customers report up to 50 percent improvement.

Reduce backup times and backup storage

• Free up existing media to store more backups.

• Keep individual backups for a longer time.

• Add backup compression on databases already compressed with data compression for further savings.

• Restore compressed backups more quickly, minimizing service interruptions.

USC-2 Unicode compression enables compression of Unicode data types, such as nchar and nvarchar.

Use the Unicode compression solution

• Take advantage of the standard SCSU compression technique.

• Avoid the need for changing the application.

• Reduce storage requirements up to 50 percent.

• Experience performance benefits similar to non-Unicode data compression.

With configuration options, developers can tailor settings to suit unique development requirements.

Configure row compression

• Store data in a variable-length format.

• Search encrypted data using both range and fuzzy searches.

• Define column data flexibly, even by using a nonvariable data type, such as char or integer.

Compress data

• Weigh CPU usage cost against potential storage and performance improvements.

• Use page compression on infrequently accessed or archived data on a partitioned table.

• Use row compression for partitions with more frequently accessed data on the same table.

• Compress only parts of a database, leaving other parts uncompressed.