Difference between revisions of "CREATE INDEX"
Yvonnemilne (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
| (3 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
==Purpose== | ==Purpose== | ||
Creates an index for the specified table | Creates an index for the specified table | ||
| Line 17: | Line 10: | ||
==See Also== | ==See Also== | ||
| − | [[ALTER INDEX]], [[ALTER TABLE]], [[CREATE TABLE]], [[DROP INDEX]], [[ | + | [[ALTER INDEX]], [[ALTER TABLE]], [[CREATE DATABASE]], [[CREATE TABLE]], [[DROP INDEX]], [[OPEN DATABASE]] |
| − | + | ||
==Description== | ==Description== | ||
| Line 47: | Line 39: | ||
<code lang="recital"> | <code lang="recital"> | ||
// Create an index on staff number | // Create an index on staff number | ||
| − | CREATE INDEX staff_no | + | CREATE INDEX staff_no; |
| − | ON staff (staff_no) | + | ON staff (staff_no) |
// Create an index on descending hire date and ascending staff number | // Create an index on descending hire date and ascending staff number | ||
| − | CREATE INDEX hiredate | + | CREATE INDEX hiredate; |
| − | ON staff (hiredate DESC, staff_no ASC) | + | ON staff (hiredate DESC, staff_no ASC) |
// Create an index on last name converted to lower case | // Create an index on last name converted to lower case | ||
| − | CREATE INDEX lname | + | CREATE INDEX lname; |
| − | ON staff (lower(lastname)) | + | ON staff (lower(lastname)) |
</code> | </code> | ||
==Products== | ==Products== | ||
| − | Recital | + | Recital Server, Recital |
[[Category:Documentation]] | [[Category:Documentation]] | ||
[[Category:SQL]] | [[Category:SQL]] | ||
[[Category:Commands]] | [[Category:Commands]] | ||
Latest revision as of 10:29, 22 December 2009
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
