Creates a new index on a given table.

Syntax

---

CREATE INDEX <Name> ON <TableName>|<ViewName>
(<ColumnName> [COLLATE <CollationName>]
   [[ASC|ASCENDING]|[DESC|DESCENDING]] [,<ColumnName>])
[DESCRIPTION <Description>]
[NO BACKUP FILES]

Usage

---

Use this statement to create a new index on a table or non-updateable view. Multiple columns can be defined for an index, however it is recommended that you try to keep the number and size of the columns, and subsequently the size of the index keys in the index, to a minimum for performance purposes.

If a collation is specified for a CHAR, VARCHAR, or CLOB column, it overrides the default collation specified for the column being included in the index.

The NO BACKUP FILES clause is optional. Unless this clause is specified, ElevateDB will create backup files (*.old) of any physical table files that were altered during the execution of the statement. Also, this clause does not apply to physical backup files created for the database catalog, which are always created and retained.

As of ElevateDB 2.21, you can now index non-updateable views. Non-updateable views are views that generate a static, insensitive result set.


Examples

---

-- The following statement creates a Name index on the
-- Customer table consisting of the Name column in
-- ascending order.

CREATE INDEX "Name" ON "Customer" (Name ASC)

-- The following statement creates a State index on the
-- Customer table consisting of the State column in
-- ascending order.

CREATE INDEX "State" ON "Customer" (State ASC)

Required Privileges

---

The current user must be granted the CREATE privilege on the specified table in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: