CREATE INDEX

Class

SQL Applications

Purpose

Creates an index for the specified table

Syntax

CREATE [UNIQUE] INDEX <index> ON <table> (<column> [ASC | DESC] [,...])

CREATE [UNIQUE] INDEX <index> ON <table> (<expression> [ASC | DESC])

See Also

ALTER INDEX, ALTER TABLE, CREATE TABLE, DROP INDEX, SET TCACHE

Description

The CREATE INDEX command creates an index file for the specified table. An index is a file which contains an entry for each value in the specified <column-name> of the CREATE INDEX statement. These values are known as key fields. Recital uses indexes to locate specific rows without reading the whole table. When changes are made to the table, such as column additions, deletions, or changes in key field values, the index will be updated as the table is updated. The table must be able to be locked for exclusive use during the operation.

Keywords	Description
UNIQUE	Specifies that the index being created cannot contain any duplicate keys.
index	This is the name of the index being created.
table	This is the name of the table for which the index will be created.
column	This is the name of the column that will be indexed. Multiple columns can be added from the same table separated with commas.
expression	The expression on which to index.
ASC	Ascending order is the default index creation.
DESC	This will create the index in descending order.

Example

// Create an index on staff number
CREATE INDEX staff_no
  ON staff (staff_no);
 
// Create an index on descending hire date and ascending staff number
CREATE INDEX hiredate
  ON staff (hiredate DESC, staff_no ASC);
 
// Create an index on last name converted to lower case
CREATE INDEX lname
  ON staff (lower(lastname));

Products

Recital Database Server, Recital Mirage Server, Recital Terminal Developer