create index

An index or index partition can be compressed with the index_compression clause.


create [unique] [clustered | nonclustered] index index_name
	on [[database.]owner.]table_name
	(column_expression [asc | desc]
	[, column_expression [asc | desc]]...)
	[with {fillfactor = pct, 
	index_compression = { NONE | PAGE },
	max_rows_per_page = num_rows,
	reservepagegap = num_pages,
	consumers = x, ignore_dup_key, sorted_data,
	[ignore_dup_row | allow_dup_row],
	statistics using num_steps values}]
	[on segment_name]
Syntax to create index partitions
[local index 
[partition_name [on segment_name] 
[with index_compression = { NONE | PAGE }]
[, partition_name [on segment_name] 
[with index_compression = { NONE | PAGE }]...]]]

Parameter Changes



Example 1

Creates a compressed index called idx_order_line on columns ol_delivery_d and ol_dist_info:

create index idx_order_line 
    on order_line (ol_delivery_d, ol_dist_info)  
with index_compression = page

If the index has an index row length that is too short to benefit from compression, a warning is raised indicating the index will not be compressed.

Example 2

Creates a compressed index called idx_Sales. The index contains local index partitions that can be compressed. Index prefix compression is applied to the local index partition. Page prefix compression is applied while the index page is full:

create index idx_sales
    on Sales(store_id, order_num) 
    local index ip1 with index_compression = PAGE, 
    ip2 with index_compression = PAGE,
Related concepts
Creating a Compressed Index