Index compression in a relational database allows more-efficient data storage,
reduced memory consumption, and improved performance due to lower I/O demands.
Index compression supports:
- Index leaf page compression
- Both DOL and APL index leaf page formats
- Compression at the table, index, and local index partition levels
You can enable or disable index compression at the server level, or at the session
level, using sp_configure and set compression.
To specify index compression at the table, index, or local index partition levels,
use these commands:
- create table
- alter table
- create index
- alter index
- select into
Specifying compression at the index level overrides index compression specified at
the table level. Local index partition level specification overrides index-level
specification.
Support
- reorg rebuild has been extended to support index compression.
- DML has been extended to support index compression.
- Index compression is supported by triggers.
- Data that has been bulk-copied into a table with indexes defined as compressed,
is automatically compressed.
- Both dbcc checktable and dbcc checkstorage
can check the integrity of indexes. These commands have been enhanced to check
the integrity of each page by scanning all pages of a table. dbcc
checktable has greater checking capacity because more information
is checked.
- dump database, dump transaction, and
load transaction are allowed if this database contains
tables with a compressed index. However, XPDL is not allowed if there are any
tables with compressed indexes.
Replication indexes are always created as uncompressed, even when compression is
specified for all indexes during table creation.
APL-clustered indexes on index-compressed tables are not supported.
Unique indexes with only one column are not compressed.