Creating an Index Compressed Table

To designate index compression for a table, use the create table or select into commands.

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 the index-level specification.

Using the create table Command

The with index_compression clause provides these compression options:

NONE – indexes on the specified table are not compressed. Indexes that are specifically created with index_compression = PAGE are compressed.
PAGE – all indexes on the specified table are compressed. Indexes that are specifically created with index_compression = NONE are not compressed.

If compression has not been specified anywhere in the table DDL, indexes for the table are not compressed.

Using the select into Command

Use select into to create an index compressed table by selecting an existing table. The syntax for the with index_compression cause is the same as for the create table command.