Database Reference
In-Depth Information
Implementing compressed storage features
for tables and indexes
Whenever data is inserted into a table or an index is rebuilt, the database engine requires
more space in the database, which is usually the underlying storage where the data file is
located. The database engine needs the process to determine where space is available within
the pages to be allocated. Therefore, the insert process must find a mixed extent with one or
more pages available that can be allocated, if the index or data is more than eight pages or
larger than what a free uniform extent needs to be allocated.
SQL Server 2008 has introduced the data compression feature as a part of the
programmability enhancements. The main benefit of compression is that it can be enabled
or disabled at object level, which means at database-wide, page-level, or row-level. In this
recipe, we will go through the important steps in implementing compressed storage features
for tables and indexes. When the compression option is configured on a partitioned table and
index, then all the partitions of an object do not have the same compression setting. However,
when partitions are split then both partitions will inherit the same data compression attribute
from the original partition.
Getting ready
The following points must be considered in order to implement the compressed
storage features;
F The SQL Server edition must be Data Center, Enterprise, or Developer (for
Development purposes only)
F The application database chosen for this implementation must represent the
OLTP environment, which means both read-only and read/write transactions are
implemented ranging from simple to fairly complex
F The database consists of medium and large tables with primary keys, foreign keys,
clustered/non-clustered indexes, composite indexes, and reference integrity checks
F The database that will be involved and underlying columns in the tables must span
a wide variety of data types with rows populated on Unicode data (multi-lingual); by
default SQL Server 2008 R2 enables compression for Unicode UCS-2 data
F Ensure that the data in the table holds numeric, varchar, datetime, and fixed-length
character data types to take advantage of data compression significantly
F Ensure that data holds NULLABLE columns where the number of rows has a NULL
value for the column
F Ensure that the tables store scores of repeating data values or repeating prefix
values in the data
 
Search WWH ::




Custom Search