SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create an index on the table.

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

Indexes

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

Users cannot see indexes, they can only be used to speed up searches/queries.

Note:Updating a table that contains an index takes more time than updating a table without an index, because the index itself also needs to be updated. Therefore, it is advisable to create indexes only on columns (and tables) that are frequently searched.

SQL CREATE INDEX Syntax

Create a simple index on the table. Allows duplicate values:

CREATE INDEX index_name
ON table_name (column_name)

Note:"column_name" specifies the column that needs to be indexed.

SQL CREATE UNIQUE INDEX Syntax

Create a unique index on the table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE INDEX Example

This example will create a simple index named "Index_Pers" on the LastName column of the Person table:

CREATE INDEX Index_Pers
ON Person (LastName)

If you want to sort byDescendingTo index the values in a column, you can add a reserved word after the column name DESC:

CREATE INDEX Index_Pers
ON Person (LastName DESC)

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

CREATE INDEX Index_Pers
ON Person (LastName, FirstName)