An Overview Of SQL Server Data Compression Technique

What Is Data Compression In SQL Server?

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 in SQL Server 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:

• A table stored as a heap.
• Partitioned tables and indexes.
• A table stored as a clustered index.
• A non-clustered index.
• An indexed view.

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.

Data Compression In SQL Server – Know its Different Types

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:
  • Storing fixed-length data types as variable-length data type.
  • Partitioned tables and indexes.
  • Storing fixed-length data types as if they are variable-length data types.
  • Not storing zero or NULL values.
  • Reducing the amount of metadata.

  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.
  • It starts with row-level data compression and firstly compress the row.
  • Then, prefix compression is performed. It removes the repeating patterns in the beginning of values of given column across all rows on each page.
  • At last, dictionary compression is performed. It searches repeated values anywhere on each page and stores them in the compression information structure.

  The result of page-level data compression in SQL Server 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.

• Backup compression: Backup compression does not use row-level data compression or page-level data compression. It performs at the time of a backup, and it uses its own compression technique. It compresses the backup of the database.

Data Compression In SQL Server – Benefits

Data compression in SQL server offers multiple benefits. Some of those are mentioned as below:

• Shrink the size of physical disk space.
• Reduces disk I/O read and write operation.
• Reduces the amount of data cache memory.
• Remove empty space in the database table.

Conclusion

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.