CREATE INDEX

From Recital Documentation Wiki
Revision as of 14:11, 19 March 2009 by Yvonnemilne (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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