CREATE INDEX Command

<< Click to Display Table of Contents >>

Navigation:  SQL Reference > SQL Commands >

CREATE INDEX Command

Previous pageReturn to chapter overviewNext page

 

CREATE INDEX defines a new index on an existing table.

Syntax

CREATE [ UNIQUE ] INDEX index_name ON [ MEMORY ] table_name

( column_name [ ASC | DESC ] [ CASE | NOCASE ] [, ... ] )

Description

CREATE INDEX constructs an index index_name on the specified table. Indexes are primarily used to enhance database performance (though inappropriate use will result in slower performance).

Multi-column indexes are allowed.

 

UNIQUE

Causes the database engine to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

 

index_name

The name of an index to be created.

 

MEMORY

If MEMORY keyword is specified before the table_name then an in-memory table is referenced, not a disk one.

 

table_name

The name of the table to be removed.

 

column_name

The name of a column to be indexed.

 

ASC

The column will be indexed with ascending sort order. This is the default.

 

DESC

The column will be indexed with descending sort order.

 

CASE

The column will be indexed with case-sensitive sort order. This is the default.

 

NOCASE

The column will be indexed with case-insensitive sort order.

 

Example:

CREATE UNIQUE INDEX idxUniqueName ON developers (name DESC NOCASE, code);

__________________________
Absolute SQL Reference - 06 February 2021, Niels Knabe