create index

Syntax

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]
[index_partition_clause]
Syntax to create index partitions
index_partition_clause::=
[local index 
[partition_name [on segment_name] 
[with index_compression = { NONE | PAGE }]
[, partition_name [on segment_name] 
[with index_compression = { NONE | PAGE }]...]]]

Parameter Changes

index_compression

• NONE – the index page for the specified index is not compressed. Indexes that are specifically created with index_compression = PAGE are compressed.
• PAGE – when the page is full, existing index rows are compressed using the page prefix compression. When a row is added, a check determines whether the row suitable for compression.

Examples

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,
ip3