February 4th, 2016
November 12, 2022
Microsoft SQL Server supports different compression types for tables and indexes, and also supports archival compression for them. The data compression feature in SQL Server is very helpful in reducing the size of the database file. Being an intensive workload because of the heavy storage of data. Data compression in SQL Server helps to improve the performance of I/O.
There are three major compressions used in MS SQL Database and you can go through all of them.
Non-leaf levels of pages in the database use ROW-level compression. Data rows are put on the page serial-wise, starting immediately after the header. The maximum amount of data and overhead contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable-length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting from the column with the largest width.
When Row-level compression takes place:
Page compression consists of three different operations in the following order:
When you use page compression, non-leaf level pages of indexes are compressed by using only Row compression.
For each page that is being compressed, prefix compression uses the following steps:
After prefix compression has been completed, dictionary compression is applied to the database. Dictionary compression searches for repeated values anywhere on the page and stores them in the CI area. Unlike prefix compression, dictionary compression is not restricted to one column. Dictionary compression can replace repeated values that occur anywhere on a page.
SQL server compresses Unicode values that are stored in a row or page-compressed objects. The Database Engine stores Unicode data as 2 bytes, regardless of locale. This is known as UCS-2 encoding. Unicode compression supports the fixed-length nchar (n) and nvarchar (n) data types. Data values that are stored off-row or in nvarchar (max) columns are not compressed with Unicode compression.
You have seen that there are three types of compression in SQL Server and they are Row Compression, Page Compression, and Unicode Compression. But you cannot compress the SQL Database if it is corrupted or damaged. Therefore, to fix the corruptions, you can use the SQL Database Recovery Software. After repairing the Master SQL Database, you can easily compress your database.