Compression is a process of reducing file and data size in memory. While there are many processes by which data may be compressed, SQL Server 2012 uses two forms: page compression and column store indexes. All compression is faster to read and slower to write. If the database environment is one where data will be rarely updated or changed, but read often, compression has definite advantages in database speed performance.
Page compression consists of three processes: Row, Prefix, and Dictionary.
- Row Compression is based on the type of data stored, and affects the amount of metadata of a record.
- Prefix Compression affects the first characters of column data and is reduced in size with a reference number left in the page header.
- Dictionary Compression, also called deduplication, applies to all data on a page.
Dictionary Compression will look through tables and indexes for common, duplicate data. It will create a reference list of data that is duplicated throughout the table or index then replaces it with a smaller reference number. The amount of compression and query time savings depends on the amount of duplicate data replaced. Dictionary Compression is read-write capable.
A user can choose between just Row compression or Page Compression. If Row Compression is activated, it does not use Dictionary Compression and is not compatible with activating Page Compression. However, Page Compression comes with Row Compression when activated.
A Columnstore Index compresses column data for faster access and analysis. It is best used in data warehousing applications where data is primarily read-only. Columnstore may or may not be updatable, depending on whether the table is clustered or non-clustered. If it is clustered, it is updateable. If it is non-clustered, it is not updateable without creating a duplicate table.
SQL Safe Backup uses dynamic compression to back up quickly and efficiently. SQL Safe’s new patent-pending IntelliCompress2™ technology continually samples system resource usage and automatically adjusts the compression level to ensure the best compression based on the state of the environment.
Idera also released a new tool in 2014 that specifically focuses on boosting data transfer performance while reducing bandwidth by using dynamic and advanced TDS compression. Tabular Data Stream (TDS) is an application layer protocol designed to transfer data between an SQL Server database and a client. TDS permits client and server products to communicate in any operating-system platform, server release or network transport.
Advanced TDS compression and optimization compresses TCP/IP packets, eliminating false acknowledgments and improving throughput. SQL Traffic Accelerator can improve performance during replication, SQL Server Integration Services (SSIS), data analysis or when running applications.