--- PDF PAGE 242 ---
225
column. The second area of metadata contains details such as versioning information 
and forwarding pointers for heaps.
Page Compression
When you implement page compression, row compression is implemented first. Page 
compression itself is actually comprised of two different forms of compression. The first 
is prefix compression and the second is dictionary compression. These compression types 
are outlined in the following sections.
Prefix Compression
Prefix compression works by establishing a common prefix for a column across rows 
within a page. Once the best prefix value has been established, SQL Server chooses the 
longest value that contains the full prefix as the anchor row and stores all other values 
within the column, as a differential of the anchor row, as opposed to storing the values 
themselves. For example, Table 7-2 details the values that are being stored within a 
column and follows this with a description of how SQL Server will store the values using 
prefix compression. The value Postfreeze has been chosen as the anchor value, since it 
is the longest value that contains the full prefix of Post, which has been identified. The 
number in <> is a marker of how many characters of the prefix are used.
Table 7-2.  Prefix Compression Differentials
Column A Value
Column A Storage
Column B Value
Column B Storage
Postcode
<4>code
Teethings (Anchor)
—
Postfreeze (Anchor)
—
Teacher
<2>acher
Postpones
<4>pones
Teenager
<3>nager
Postilion
<4>ilion
Teeth
<5>
Imposters
<0>Imposters
Tent
<2>nt
Poacher
<2>acher
Rent
<0>Rent
Chapter 7  Table Optimizations

--- PDF PAGE 243 ---
226
Dictionary Compression
Dictionary compression is performed after all columns have been compressed using 
prefix compression. It looks across all columns within a page and finds values that 
match. The matching is performed using the binary representation of a value, which 
makes the process data type agnostic. When it finds duplicate values, it adds them to a 
special dictionary at the top of the page, and in the row, it simply stores a pointer to the 
value’s location in the dictionary. Table 7-3 expands on the previous table to give you an 
overview of this.
Table 7-3.  Dictionary Compression Pointers
Column A Value
Column A Storage
Column B Value
Column B Storage
Postcode
<4>code
Teethings 
(Anchor)
—
Postfreeze (Anchor)
—
Teacher
[Pointer1]
Postpones
<4>pones
Teenager
<3>nager
Postilion
<4>ilion
Teeth
<5>
Imposters
<0>Imposters
Tent
<2>nt
Poacher
[Pointer1]
Rent
<0>Rent
Here, you can see that the value <2>acher, which appeared in both columns in the 
previous table, has been replaced with a pointer to the dictionary where the value is 
stored.
Page Compression Structure
In order to facilitate page compression, a special row is inserted in the page immediately 
after the page header, which contains the information regarding the anchor record and 
dictionary. This row is called the compression information record, and it is illustrated in 
Figure 7-12.
Chapter 7  Table Optimizations

--- PDF PAGE 245 ---
228
Implementing Compression
The planning and implementation of row and page compression is a fairly 
straightforward process, and it is discussed in the following sections.
Selecting the Compression Level
As you probably realized from the earlier descriptions of row and page compression, 
page compression offers a higher compression ratio than row compression, which 
means better IO performance. However, this is at the expense of CPU cycles, both when 
the table is being compressed and again when it is being accessed. Therefore, before 
you start compressing your tables, make sure you understand how much each of these 
compression types will reduce the size of your table by so that you can assess how much 
IO efficiency you can achieve.
You can accomplish this by using a system stored procedure called sp_estimate_
data_compression_savings. This procedure estimates the amount of space that you 
could save by implementing compression. It accepts the parameters listed in Table 7-4.
Table 7-4.  sp_estimate_data_compression_savings Parameters
Parameter
Comments
@schema_name
The name of the schema, which contains the table that you want to run 
the procedure against.
@object_name
The name of the table that you want to run the procedure against.
@index_ID
Pass in NULL for all indexes. For a heap, the index ID is always 0 and a 
clustered index always has an ID of 1.
@partition_number
Pass in NULL for all partitions.
@data_compression
Pass in ROW, PAGE, COLUMNSTORE, COLUMNSTORE_ARCHIVE, or NONE 
if you want to assess the impact of removing compression from a table 
that is already compressed.
The two executions of the sp_estimate_data_compression_savings stored 
procedure in Listing 7-11 assess the impact of row and page compression, respectively, 
on all partitions of our ExistingOrders table.
Chapter 7  Table Optimizations