Implementing Page Compression

Page compression can be implemented for a table at the time it is created or by using the ALTER TABLE command, as in the following example:

Click here to view code image

ALTER TABLE Sales.SalesOrderDetail REBUILD WITH (DATA_COMPRESSION=PAGE)

Unlike row compression, which is applied immediately on the rows, page compression isn’t applied until the page is full. The rows cannot be compressed until SQL Server can determine what encodings for prefix and dictionary substitution are going to be used to replace the actual data. When you enable page compression for a table or a partition, SQL Server examines every full page to determine the possible space savings. Any pages that are not full are not considered for compression. ...

Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial