create table for Index Compression

Syntax

create table [database.[owner].]table_name 
(column_name datatype
[default {constant_expression | user | null}]
{[{identity | null | not null}]
[off row | [in row [(size_in_bytes)]]
[[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)}]}
[match full]...
[encrypt [with key_name] 
    [decrypt_default constant_expression | null]]
[[constraint [[database.[owner].]key_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) ...}
 [{, {next_column | next_constraint}}...]
 [lock {datarows | datapages | allpages}]
[with {max_rows_per_page = num_rows,
	exp_row_size = num_bytes,
	reservepagegap = num_pages,
	identity_gap = value,
	transfer table [on | off], 
	compression [={NONE | ROW | PAGE}],
	index_compression [={NONE | PAGE} ]
      }
]
[on segment_name]	
 	[[ external table ] at pathname ]
[partition_clause]

Parameter Changes

index_compression

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.

Example

This example creates the index compressed table order_line with columns ol_delivery_d and ol_dist_info compressed and using page-level compression:

create table order_line (          
    ol_o_id         int,          
    ol_d_id         tinyint,          
    ol_w_id         smallint,          
    ol_number       tinyint,          
    ol_i_id         int,          
    ol_supply_w_id  smallint,          
    ol_delivery_d   datetime,
    ol_quantity     smallint,          
    ol_amount       float,          
    ol_dist_info    char(24) ) 
lock datapages   
    with index_compression = page

By default, indexes created on this table are compressed by default. However, if an index has an index row length that is too short to benefit from compression, a warning is raised, indicating that the index will not be compressed.