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

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF