Data compression is a feature in MS SQL server that helps to reduce a size of the database. It also improves the performance of Input/output intensive workloads. The idea of data compression is that users can selectively choose indexes, tables, or partitions within a database and reduce the amount of space used to store the data. By using SQL Server’s data compression feature, users can improve SQL Server performance without making changes in application code. Compression of data reduces the storage of database, which leads to fewer disk I/O reads and writes. Data compression can be used for below-mentioned database objects:
Data compression is currently supported in the Developer, Enterprise, and Evaluation editions SQL Server 2008 and later versions of it. Data compression in SQL server requires faster CPU and more memory space.
SQL server 2008 or later versions mainly provide two type of data compression i.e., row-level compression and page-level compression. These compression occur on the MDF files of databases. On the hand, there is another compression i.e., backup compression that occurs when data is backed up.
Row-level Data Compression:Row-level data compression helps to store data efficiently in a row by storing fixed-length data types into variable-length data types. It removes free space in the database table. It ignores zero and null values for saving additional space. Row-level data compression works by:
Row-level data compression performs less compression than page-level data compression.
Page-level Data Compression: Page-level compression is a superset of row-level compression. It has a feature of row-level compression as well as two additional compression features i.e., prefix and dictionary compression. It offers greater compression. It works using below-mentioned techniques.
The result of page-level data compression is dependent on the data stored in the database table. If a database has more repeated data, then this compression is more efficient. If the data is more random, then some benefits can be gained using page-level data compression.
Note: The major difference between prefix and dictionary data compression is that prefix compression is restricted to only one column, while dictionary compression works anywhere on each page.
Data compression in SQL server offers multiple benefits. Some of those are mentioned as below:
The data compression feature in SQL server helps to compress data within a database and it reduce the size of a database. SQL Server currently supports two types of data compression i.e. row-level data compression and page-level data compression. Row-level compression changes fixed-length data type into variable length data type. Page-level compression performs row-level compression and two other compressions i.e. prefix compression and dictionary compression. Therefore, in this post, we have discussed two types of data compression in SQL server and about backup compression in detail.