A SQL Server feature that reduces the physical storage footprint of tables and indexes using row-level or page-level compression, without changing how data is queried.
- SQL Server offers two compression levels: Row compression stores data in a more compact format by eliminating fixed-length padding, while Page compression applies row compression plus prefix and dictionary compression across pages.
- Compression reduces storage costs, decreases I/O during reads, and can improve query performance: These benefits come at the cost of slightly higher CPU usage for compression and decompression.
- Backup compression is a separate feature: It reduces backup file sizes and transfer times, typically achieving a 50–80% reduction, and is generally recommended for all backup strategies.
- DBAs can evaluate compression savings before implementation: The
sp_estimate_data_compression_savingsstored procedure can be used for this purpose. - Compression is transparent to applications: Queries and indexes work identically on compressed and uncompressed data.
- Relevant Idera tools: SQL Safe Backup uses advanced compression to minimize backup file sizes and accelerate backup windows.
- Related terms: Backup, Page Split, Columnstore Index, Storage Optimization.
