Enabling Index Compression

Enable index compression at the server level or at the database level.

Enabling Index Compression at the Server Level

To enable compression on all indexes in all databases on a server, set index compression at the server level.

The syntax is:

sp_configure "enable compression", 0 | 1

The default value is 0.

If index compression is not enabled, an error is raised when you attempt to create an index compressed table or index.

To enable compression for all indexes in all databases of a session, set index compression at the database level.

The syntax is:

set {compression 
   [= {default | ON | OFF} ]  
   |index_compression 
   [= {default | ON | OFF} ] }

The default value is off. This command affects only leaf rows that are built for compressed indexes after the command is executed.

After set index_compression is set to off, all rows that are newly inserted into compressed index are not index compressed.
After set index_compression is set to on, all rows that are newly inserted into compressed index are index compressed.