create index

Creates an index on one or more computed or noncomputed columns in a table. Creates partitioned indexes. Allows computed columns, like ordinary columns, to be index keys, and creates function-based indexes. A function-based index has one or more expressions as its index key.

The existing create index syntax can create indexes on computed columns, but function-based indexes require additional syntax.

Syntax

create [unique] [clustered | nonclustered] index index_name 
	on [[database.][owner].]table_name 
		 (column_expression [asc | desc]
			[, column_expression [asc | desc]]...) 
	[with {fillfactor = pct, 
			online,
            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 ] 
            [, statistics hash_option]                        
            [, statistics max_resource_granularity = int]             
            [, statistics histogram_tuning_factor = int]
            [, statistics print_progress = int] } ]
    [on segment_name]
	[index_partition_clause]
    [with index_compression = { NONE | PAGE }]...]]]
To create index partitions:
index_partition_clause::=
	[local index [partition_name 
	[with index_compression = { NONE | PAGE }][on segment_name]
    [, partition_name [on segment_name]
    [with index_compression = { NONE | PAGE }][on segment_name]...]]
For function-based indexes:
create [unique | nonclustered] index index_name
	on [[database.] [owner].] table_name
	 (column_expression [asc | desc] 
	[, column_expression [asc | desc]]...

Parameters

Examples

Usage

See also:
  • sp_addsegment, sp_chgattribute, sp_helpcomputedcolumn, sp_helpindex, sp_helpsegment, sp_spaceused in Reference Manual: Procedures

  • optdiag in the Utility Guide

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for create index differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the table owner, or a user with the create any index privilege.

Disabled

With granular permissions disabled, you must be the table owner or a user with sa_role.

create index permission defaults to the table owner and is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

104

Audit option

create

Command or access audited

create index

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of the index

  • Proxy information – original login name, if a set proxy is in effect

Related reference
alter table
create table
drop index
insert
order by clause
set
update
reorg