Page Compression In SQL Server
In this post we will learn about Page Compression in SQL Server. How to implement Row Compression in SQL Server and benefits of it. In earlier post we had discussed about Row Compression in SQL Server
.
Page Compression:
Page compression further extends row compression by performing a few additional steps. Page compression performs three operations:
1. Row compression
2. Prefix compression
3. Dictionary compression
Page compression includes row compression as part of its process. Nothing changes with the row compression process-it’s just the first step in page compression. After the row compression is complete, the next step is prefix compression.
During this step, each column is scanned for a value that will reduce the storage space for each column. Once the value is identified, a row for each column is stored in the page header. All the information is called the compression information (CI), which is stored below the page header. The identified values (prefixed values) are located in each column and replaced with a pointer to the value in the CI section.
The next step is dictionary compression, which scans the entire page instead of a single column.
Page Compress using SSMS:
1. Open SQL Server management studio.
2. Expand Server node > Databases > Database > Table.
3. Right click on table which you want to compress, and click on storage > manage compression.
4. When the Data Compression Wizard appears, check the box labeled Do Not Show This Starting Page Again, and click Next.
5. Check the Checkbox labeled Use Same Compression Type for All Partitions.
6. Now Select Page from drop down list, and click on calculate as shown in above image.
7. Click Next, next window gives you options like when and how you want to compress the data.
8 Click on Finish.
Page compression with T-SQL:
USE [Lab] ALTER TABLE [dbo].[Gridviewimage] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE )