create index (core)

Syntax

Transact-SQL Syntax

create [unique][clustered | nonclustered]
 index index_name

on [[database.]owner.]table_name(column_name
[, column_name]...)

[with {{fillfactor | max_rows_per_page} = x, ignore_dup_key, sorted_data,
 [ignore_dup_row | allow_dup_row]}]

[on segment_name]

ODBC Syntax

CREATE [UNIQUE] INDEX index_name

ON base_table_name

(column_identifier[ASC|DESC]
 [,column_identifier[ASC|DESC]]...)

Parameters

• unique – prohibits duplicate index values (key values). The system checks for duplicate key values when an index is created and checks each time data is added with an insert or update. If a duplicate key value exists, or if more than one row contains a NULL value, the command aborts and an error message shows the duplicate value prints.
• clustered – indicates that the physical order of rows on this table is the same as the indexed order of rows. Only one clustered index per table is permitted.
• nonclustered – indicates that a level of indirection exists between the index structure and the data. Up to 249 nonclustered indexes per table are permitted.
• fillfactor – specifies how full the DBMS makes each page when it creates a new index on existing data. This percentage is relevant only at the time the index is created. As the data changes, the pages are not maintained at any level of fullness. The default is 0. If the fillfactor is set to 100, the DBMS creates indexes with pages 100% full.
• ignore_dup_key – responds to a duplicate key entry into any table with a unique index. Any attempted insert of a duplicate key is ignored, and the insert is cancelled with an informational message.
• on segment_name – specifies that the index is to be created on the named segment.
• index_name – is the name of the index. Index names must be unique within a table but need not be unique within a database.
• table_name – is the name of the table that contains the indexed column or columns.
• column_name – is the column or columns to be included in the index. Composite indexes are based on the combined values of up to 16 columns. The sum of the maximum lengths of all the columns used in a composite index cannot exceed 256 bytes.

Examples

• Example 1 – creates an index of aud_id.

  create index au_id_ind
  on authors (au_id)

• Example 2 – creates an index of title_id.

  create index ind1
   on titleauthor (au_id, title_id)

• Example 3 – creates a nonclustered index of zip_ind with fillfactor.

  create nonclustered index zip_ind
  on authors (zip) with fillfactor = 25

Usage