View source for CREATE INDEX

==Purpose==
Creates an index for the specified table


==Syntax==
CREATE [UNIQUE] INDEX <index> ON &#060;table&#062; (<column> [ASC | DESC] [,...])

CREATE [UNIQUE] INDEX <index> ON &#060;table&#062; (<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.


{| class="wikitable"
!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==
<code lang="recital">
// 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))
</code>


==Products==
Recital Server, Recital 
[[Category:Documentation]]
[[Category:SQL]]
[[Category:Commands]]