Page Compression in SQL Server

SQL Server supports page compression which is similar for tables, table partitions, indexes, and index partitions inside a database to reduce the size of the database which improve performance of I/O intensive workloads. Data is compressed by storing repeating values or common prefixes only once.  Whenever page-level compression is employed, row-level compression is switched on as well.

Compressing the leaf level of tables and indexes with page compression consists of three operations in the following order:

1. Row compression
2. Prefix compression
3. Dictionary compression.

Whenever we use page compression, non–leaf-level pages of indexes are compressed by using only row compression.

Prefix Compression: For each page that is being compressed, prefix compression uses the following steps:

• For each column, a value is identified that can be used to reduce the storage space for the values in each column.
• A row that represents the prefix values for each column is created and stored in the compression information (CI) structure that immediately follows the page header.
• The repeated prefix values in the column are replaced by a reference to the corresponding prefix. If the value in a row does not exactly match the selected prefix value, a partial match can still be indicated.

How does Page Compression work?

To understand this functionality, we are created a table called PageCompression having two columns, one column would be numeric and another one would be string type. We are going to insert 100000 records in this table and will measure the occupied space also as given below:

------ Create table: PageCompression CREATE TABLE PageCompression   (      Id INT,     DataValue VARCHAR(50)    ) ----- Declare local variable to incremental with default value DECLARE @ictr INT =1 ----- Insert Value into the PageCompression WHILE @ictr <= 100000   BEGIN   ----- Insert value and casting for int to varchar       INSERT INTO PageCompression (Id, DataValue)       VALUES (@ictr, 'PageCompression for:'+ Cast(@ictr as Varchar(10)))   ----- set incremental here       SET @ictr = @ictr + 1   END   ---- after load values check the space of the table SP_SpaceUsed PageCompression

In the above table, you can see that it’s using 4296KB as reserved and 4248 KB for data size. To compress the data we are going to Page compression on this table as given below:

---- Alter TABLE with data compression technique ---- by using data_compression = PAGE ALTER TABLE dbo.PageCompression REBUILD WITH (data_compression = PAGE)   ---- after applying data compression technique on table   ---- check occupied space again SP_SpaceUsed PageCompression

You can see that after applying row compression on the table, reserved and data size have been decreased accordingly 1160KB and 1152KB.

Conclusion

Page compression enhances row compression by adding prefix compression and then dictionary compression to row compression…so, in order, a page compression operation first compresses the row; then it determines a valid prefix for data on each page and compresses using that algorithm (prefix compression); then it looks for repeating values on each page, and compresses using that algorithm (dictionary compression).
To know more on Data Compression-
Considerations for When You Use Row and Page Compression
ColumnStore Archive Compression in SQL Server
How does Row Compression work?
What is Data Compression in SQL Server?

What is Page Compression in SQL Server?