Progress
SQL-92
Guide and Reference


CREATE INDEX Statement

Creates an index on the specified table using the specified columns of the table. An index improves the performance of SQL operations whose predicates are based on the indexed column. However, an index slows performance of INSERT, DELETE, and UPDATE operations.

SYNTAX

CREATE [ UNIQUE ] INDEX index_name 
  ON table_name 
  ( { column_name [ ASC | DESC ] } [, ... ] ) 
  [ AREA area_name ]; 

UNIQUE

Does not allow the table to contain any rows with duplicate column values for the set of columns specified for that index.

index_name

Must be unique within the local database.

table_name

The name of the table on which the index is being built.

column_name [ , ... ]

The columns on which searches and retrievals will be ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement, a concatenated index is created.

area_name

The name of the storage area where the index and its entries are stored.

ASC | DESC

Allows the index to be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

NOTES

EXAMPLE

This example illustrates how to create a UNIQUE INDEX on a table:

CREATE UNIQUE INDEX custindex ON customer (cust_no) ; 

AUTHORIZATION

Must have DBA privilege or INDEX privilege on the table.

SQL COMPLIANCE

ODBC Core SQL grammar

ENVIRONMENT

Embedded SQL, interactive SQL, ODBC applications, JDBC applications

RELATED STATEMENTS

CREATE TABLE Statement, DROP INDEX Statement


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095