SQL INDEX

SQL CREATE INDEX Statement

The CREATE INDEX statement is employed to generate indexes within tables.

These indexes facilitate faster data retrieval from the database, enhancing search and query performance, although they remain invisible to users.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

CREATE INDEX Syntax

This statement creates an index on a table where duplicate values are permitted.

CREATE INDEX index_name
ON table_name (column1, column2, …);

CREATE UNIQUE INDEX Syntax

This statement creates a unique index on a table where duplicate values are not permitted.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

Note: The syntax for creating indexes may differ between various databases. Hence, it’s important to verify the syntax for creating indexes in your specific database system.

CREATE INDEX Example

This SQL statement establishes an index named “idx_lastname” for the “LastName” column within the “Persons” table.

CREATE INDEX idx_lastname
ON Persons (LastName);

When creating an index on a combination of columns, you can specify the column names within the parentheses, separated by commas.

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

DROP INDEX Statement

The DROP INDEX statement serves to remove an index from a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE table_name
DROP INDEX index_name;