FirebirdSQL logo
 TABLEVIEW 

CREATE INDEX

Creates an index

Available in

DSQL, ESQL

Syntax
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
  INDEX indexname ON tablename
  {(col [, col ...]) | COMPUTED BY (<expression>)}
  [WHERE <search_condition>]
Table 1. CREATE INDEX Statement Parameters
Parameter Description

indexname

Index name.The maximum length is 63 characters

tablename

The name of the table for which the index is to be built

col

Name of a column in the table.Columns of the types BLOB and ARRAY and computed fields cannot be used in an index.

expression

The expression that will compute the values for a computed index, also known as an “expression index”

search_condition

Conditional expression of a partial index, to filter the rows to include in the index.

The CREATE INDEX statement creates an index for a table that can be used to speed up searching, sorting and grouping.Indexes are created automatically in the process of defining constraints, such as primary key, foreign key or unique constraints.

An index can be built on the content of columns of any data type except for BLOB and arrays.The name (identifier) of an index must be unique among all index names.

Key Indexes

When a primary key, foreign key or unique constraint is added to a table or column, an index with the same name is created automatically, without an explicit directive from the designer.For example, the PK_COUNTRY index will be created automatically when you execute and commit the following statement:

ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY
  PRIMARY KEY (ID);

Who Can Create an Index?

The CREATE INDEX statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege