Index Compression

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

Index compression is supported by:

reorg rebuild
DML
triggers
Data that has been bulk-copied into a table with indexes defined as compressed, is automatically compressed.
Both dbcc checktable and dbcc checkstorage check the integrity of indexes
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.

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

Any table or index, including temporary tables, may be designated for index compression, except of system catalogs and worktables.

Specifying compression at the index level overrides index compression specified at the table level. Local index partition level specification overrides index-level specification.

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.

Enabling Index Compression
Enable index compression at the server level or at the database level.
Creating an Index Compressed Table
To designate index compression for a table, use the create table or select into commands.
Creating a Compressed Index
To designate index compression for a index or local index partition level, use the index_compression clause.
Changing the Index Compression State
To change the compression state of the table for future index inserts or updates, use alter table or alter index.