alter table for Index Compression

Syntax

alter table [[database.][owner].table_name
{add column_name datatype}
   [default {constant_expression | user | null}]
    {identity | null | not null}
    [off row | in row]
    [[constraint constraint_name]
       {{unique | primary key}
          [clustered | nonclustered] [asc | desc]
        [with {fillfactor = pct,
               max_rows_per_page = num_rows,
               reservepagegap = num_pages}]
        [on segment_name]
| references [[database.]owner.]ref_table
      [(ref_column)] [match full]
| check (search_condition)]
[encrypt [with key_name] [decrypt_default value]],
[[not] compressed]
[, next_column]...
| add {[constraint constraint_name]
        {unique | primary key}
           [clustered | nonclustered]
           (column_name [asc | desc]
		[, column_name [asc | desc]...])
           [with {fillfactor = pct,
                  max_rows_per_page = num_rows,
                  reservepagegap = num_pages}]
           [on segment_name]
| foreign key (column_name [{, column_name}...])
     references [[database.]owner.]ref_table
        [(ref_column [{, ref_column}...])]
   [match full]
| check (search_condition)}
| set {  [ dml_logging = {full | minimal | default}]
       | [,compression = {NONE | PAGE | ROW | ALL} ] 
       | [,index_compression = {NONE | PAGE} ]
      }
| drop {column_name [, column_name]...
| constraint constraint_name}
| modify column_name
     [datatype [null | not null]]
     [[[encrypt [with key_name] 
         [decrypt_default value]]
        | decrypt
     ]
     [[not] compressed ]
[, next_column]...
| replace column_name
      default {constant_expression | user | null}
| decrypt_default {constant_expression | null}
| drop decrypt_default
| lock {allpages | datarows | datapages}
| with exp_row_size=num_bytes
| partition number_of_partitions
| unpartition
| partition_clause
| alter_partition_clause


     alter_partition_clause ::=
{add_partition_clause
| drop_partition_clause
| modify partition partition_name 
			[, partition_name ...]
   set compression [= {none | row | page | ALL} ] 
   set index_compression [= {none | page} ] 

Parameter Changes

index_compression – specifies the index compression to be enabled or disabled to the table, index, or the local index partition. If the table is modified to be index compressed, newly created indexes are compressed.

• NONE – indexes on the specified table are not compressed.

• PAGE – all indexes on the specified table are compressed.

Examples

Example 1

Alters the existing table order_line, changing the compression state to NONE:

alter table order_line set index_compress = NONE 

Example 2

Alters the existing table sales, changing the state to PAGE for compression of the local index partition Y2009.

alter table sales 
modify partition Y2009 set index_compression = PAGE