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:

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

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.