Learn Different Types of SQL Server Compression Used for MDF Database
SQL Server is basically a relational database management system, which is developed by Microsoft. The main purpose of SQL Server is to permit users to store and retrieve data whenever required. The SQL Server database contains a Master Data File (MDF), which includes multiple New Data Files (NDF) and a unique LDF file or transaction log database file. As everybody knows that the database grows with time and same is the case with MDF file it also grows with time. However, it is impossible to stop the growth of MDF file but SQL Server 2008 and above version provides some software that helps users in dealing with the data in a better way. SQL Server compression is a better way to manage MDF database size.
Microsoft SQL Server provides a different type of compression for tables and indexes. In addition, it offers archival compression also to reduce database size. Now, in order to reduce the size of MDF file, a DBA can use the data compression tool. Apart from minimizing the physical database size, it reduces a total number of disk I/O and improves the performance of database application. As MDF file is the main concern for every user using SQL Server. Therefore, in the following segments of the post, we have discussed a different type of compression used for MDF database.
Different Types to Compress SQL Server MDF File
To minimize the amount of physical disk space and to reduce disk I/O, it is important to compress MDF file using data compression. However, this data compression is further divided into two parts i.e. Row-Level and Page-Level SQL Server Compression, Which are discussed below in detail:
ROW-Level SQL Server Compression
In Row-level compression, only non-leaf levels of pages of the database are compressed. All the data rows are added serial wise on the page, immediately after the header. Moreover, single row on a page can contain the maximum amount of data is 8 KB or 8,060 bytes. If the total size of all fixed & variable columns of a table exceeds 8 KB, then SQL Server add one more variable length columns to the page dynamically in the ROW_OVERFLOW_DATA allocation unit.
Benefits of Row level compression:
- It minimizes the overhead of metadata associated with the record.
- It makes use of variable-length storage format for numeric types and for the types that are numeric based. (It changes fixed length data type to variable length data type)
- It frees up empty space by not storing blank characters, null and zeros values.
PAGE-Level SQL Server Compression
The page-level compression basically contains three different operations, which is to be followed in the same order:
- Row Compression
- Prefix Compression
- Dictionary Compression
Row Compression: In order to compress non-leaf level pages of indexes, one can use Row-level compression only.
Prefix Compression: To compress each page, prefix compression uses the below-mentioned steps:
- A unique value is identified for each column that will help in reducing the total storage space in all columns.
- A row which identifies the prefix values for each column is made and stored in the compression information (CI) structure, which follows the page header immediately.
- In last, It will replace all the repeated prefix values by the reference to the corresponding prefix in the column. However, if the selected prefix value does not match the value in the row, a partial match is indicated.
Dictionary Compression: As the prefix compression completed successfully, one can use the dictionary compression on the database. However, the main purpose of dictionary compression is to look for the repeated values in the page and maintain them in the CI area. However, it is different from prefix compression, as it is not limited to one column only. This dictionary compression replaces all repeated values that appear anywhere on the page.
The Bottom Line
In this blog, we have discussed the two type of data compression to compress the SQL Server MDF file. However, there is a different type of SQL Server compression but only data compression is used to compress MDF file. As it is an advanced feature of the SQL Server, so it is suggested to be used by experienced DBAs only. Apart from all this, the MDF file compression also helps in improving application performance using SQL Server.
