SQL > Data Definition Language (DDL) > Create Index Statement

As mentioned in the Index overview page, a table index helps SQL statements run faster. The syntax for creating an index is:

CREATE INDEX "index_name" ON "table_name" (column_name);

Note that an index can only cover one table. We cannot build an index that covers multiple tables.

Let's assume that we have the following table,

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

and we want to create an index on the column Last_Name, we would type in,

CREATE INDEX IDX_CUSTOMER_LAST_NAME
ON Customer (Last_Name);

If we want to create an index on both City and Country, we would type in,

CREATE INDEX IDX_CUSTOMER_LOCATION
ON Customer (City, Country);

A simple index is an index on a single column, while a composite index is an index on two or more columns. In the examples above, IDX_CUSTOMER_LAST_NAME is a simple index because there is only one column, while IDX_CUSTOMER_LOCATION is a composite index because there are two columns.

There is no strict rule on how to name an index. The generally accepted method is to place a prefix, such as "IDX_", before an index name to avoid confusion with other database objects. It is also a good idea to provide information on which table and column(s) the index is used on.

Please note that the exact syntax for CREATE INDEX may be different for different databases. You should consult with your database reference manual for the precise syntax.

Next: SQL DROP TABLE

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact