create index

Description

Using a language command, create index adds a new index to an existing table.

Syntax

create [unique] index index_name
on [[database.]owner.]table_name
(column_name [, column_name]...)

Parameters

unique: is an optional keyword that prohibits duplicate index values (also called key values).

index_name: is the name of the index. Index names must be unique within a table but not 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

create index au_id_ind
 on authors (au_id)

Example 2

create index ind1
 on titleauthor (au_id, title_id)

Usage

Columns of type bit, text, and image cannot be indexed.
You cannot create an index on a view.
The DB2 access service initially accepts the following T-SQL statement components but then strips them out of the statement:
- Clustered and nonclustered
- with { fillfactor = x, ignore_dup_key, sorted_data }
- on segment_name, which specifies the segment where the index is to be created