Page compression implementation

Page compression for tables, table partitions, indexes, and index partitions is similar. The following table-specific page compression instructions also apply to page compression for all object types. The following example compresses strings, but for other data types, prefix compression and dictionary compression are the same in principle.

The process of compressing the table and indexing by PAGE compression consists of three operations in the following order:

1. Row Compression

2. Prefix Compression

3. Dictionary Compression

When page compression is used, only row compression is used to compress the non-leaf-level pages of the index. For more information about row compression, see the implementation of row compression.

Prefix Compression

For each page to be compressed, follow these steps:

1. A value is determined for each column, which can be used to reduce the storage space of values in each column.

2. Create a row that represents the prefix value of each column and store it in the compressed information (CI) structure following the page header.

3. Repeated prefix values in the column are replaced by references pointing to the corresponding prefix. If the value in the row does not exactly match the selected prefix value, it still indicates that some matching exists.

A sample page of the table before prefix compression is displayed.

It displays the same page after the prefix is compressed. The prefix is moved to the page header, and the column value is changed to a reference to the prefix.

In the first column of the first row, the value 4B indicates the first four characters (aaab) and character B with the prefix displayed for the row. In this case, the result value is aaabb, which is the original value.

Dictionary Compression

After the prefix is compressed, the application dictionary is compressed. Compress the duplicate values at any location on the search page in the dictionary and store them in the CI area. Unlike prefix compression, dictionary compression is not limited to one column. Dictionary Compression can replace repeated values at any position on the page. Displays the same page after dictionary compression.

Note that the value 4B has been referenced by other columns on the page.

Page Compression

When creating a new table with page compression, It is not compressed. However, the metadata of the table indicates that
Use Page compression. When data is added to the first data page, row compression is performed on the data. Because this page is not full, page compression cannot be used to obtain any benefits. If the page is full, add the next line to compress the boot page
Operation. The page is displayed. Compress each column with a prefix and compress all columns for dictionary compression. If the page compression has created enough space for the row to be added on the page, add the row and
Compress rows and pages. If the space obtained by PAGE compression minus the Ci
If the remaining space after the required space is insufficient, the page is not compressed. Later, the row will be added to the new page. If the new page cannot accommodate more rows, a new page will be added to the table. And
The first page is similar. New pages are not compressed at first.

When an existing table that contains data is converted to page compression, each page is regenerated and computed. Re-generating all pages will result in the re-generation of tables, indexes, or partitions.