SQL - INDEX

The SQL INDEX statement is used to create indexes on a table. Indexes are useful to retrieve data from the database more quickly and speeds up the search queries. Indexes are not visible to the end users. Along with this, updating a table with indexes takes more time as compared to a table without any indexes because indexes are updated. Therefore it is advised to create indexes only on frequently searched columns.

Syntax

The syntax for using SQL INDEX statement is given below:

/* index_name can be duplicate */
CREATE INDEX index_name
ON table_name (column1, column2, ...);

/* creating unique index_name */
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

SQL INDEX statement Examples

The below mentioned SQL code creates index on column Name of Employee table.

CREATE INDEX idx_name
ON Employee (Name);

To create a index on multiple columns (Name and EmpID) of a table (Employee), the SQL code is given below:

CREATE INDEX idx_nameid
ON Employee (Name, EmpID);

To create a unique index on EmpID column of a Employee table, the SQL code is mentioned below:

CREATE UNIQUE INDEX idx_empid
ON Employee (EmpID);

DROP INDEX Statement

The DROP INDEX statement is used to delete a index on a table.

Syntax

The syntax for using DROP INDEX statement is given below:

/* MyAccess */
DROP INDEX index_name ON table_name;

/* MySQL */
ALTER TABLE table_name
DROP INDEX index_name;

/* SQL Server */
DROP INDEX table_name.index_name;

/*Oracle */
DROP INDEX index_name;