CREATE INDEX

From Recital Documentation Wiki
Revision as of 10:29, 22 December 2009 by Yvonnemilne (Talk | contribs)

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

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 DATABASE, CREATE TABLE, DROP INDEX, OPEN DATABASE

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 Server, Recital