SQL CREATE INDEXStatements

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow database applications to find data faster without reading the entire table.

Index

You can create indexes in tables to query data more quickly and efficiently.

Users cannot see indexes; they are only used to speed up searches/queries.

Note:Updating a table with indexes takes more time than updating a table without indexes, because the indexes themselves also need to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are frequently searched.

SQL CREATE INDEX Syntax

Creates a simple index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed: A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note:The syntax for creating indexes differs in different databases. Therefore, check the syntax for creating indexes in your database.

CREATE INDEX Example

The following SQL statement creates an index named "PIndex" on the "LastName" column in the "Persons" table:

CREATE INDEX PIndex
ON Persons (LastName)

If you want to create an index on more than one column, you can list the names of the columns in parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

Other Extensions