1103
Chapter 48: Data Compression
48
If the object is row compressed, or page compressed (which automatically includes row com-
pression), then row compression is always enabled for every page of the object. Page com-
pression, however, is a different story:■ The storage engine enables page compression on a page-by-page basis when there’s
a benefi t for that page. When the storage engine creates a new page, it’s initially
uncompressed and remains uncompressed as rows are added to the page.■ (^) When the page is full but SQL Server wants to add another row to it, the storage
engine tests the page for compression. If the page compresses enough to add the
new rows, then the page is compressed.
■ When the page is a compressed page, any new rows will be inserted compressed.
(But they won’t trigger recalculation of the compression information, the prefi x
anchor row, or the dictionary tokens.)
■ (^) Pages might be recompressed (and the prefi xes and dictionary tokens recalculated)
when the row is updated, based on an algorithm that factors in the number of
updates to a page, the number of rows on the page, the average row length, and the
amount of space that can be saved by page compression for each page, or when the
row would again need to be split.
■ Heaps are recompressed only by an index rebuild or bulk load.
■ (^) In the case of a page split, both pages inherit the page compression information
(compression status, prefi xes, and dictionary tokens) of the old page.
■ (^) During an index rebuild of an object with page compression, the point at which the
page is considered full still considers the fi ll factor setting, so the free space is still
guaranteed.
■ Row inserts, updates, and deletes are normally written to the transaction log in
row compression, but not in page compression format. An exception is when page
splits are logged. Because they are a physical operation, only the page compression
values are logged.
Applying Data Compression
Although data compression is complicated, actually enabling data compression is a straight-
forward task using either the Data Compression Wizard or an ALTER command.Determining the Current Compression Setting
When working with compression, the fi rst task is to confi rm the current compression set-
ting. Using the Management Studio UI, you can view the compression type for any single
object in two ways:■ (^) The Table Properties or Index Properties Storage page displays the compression set-
tings as a read-only value.
c48.indd 1103c48.indd 1103 7/31/2012 10:24:09 AM7/31/2012 10:24:09 AM
http://www.it-ebooks.info