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.

The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while performing insert or update operation, the database update the indexes as well.

SQL CREATE INDEX

Along with regular index, it is possible to create a unique index also on a table. Unique index is the same as the Primary key in SQL. The unique index indicates that the combination of values in the indexed columns must be unique. Unique indexes are used for the maintenance of the integrity of the data present in the table as well as for the fast performance.

Syntax

The syntax for using SQL CREATE INDEX statement is given below:

/* Allows duplicate combination of 
   values in the indexed columns */
CREATE INDEX index_name
ON table_name (column1, column2, ...);

/* Allows only unique combination of 
   values in the indexed columns */
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

SQL CREATE INDEX Example

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);

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

CREATE UNIQUE INDEX idx_empid
ON Employee (Name, EmpID);

SQL DROP INDEX

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

Syntax

The syntax for using SQL DROP INDEX statement is given below:

/* MySQL / MS Access / SQL Server */
DROP INDEX index_name ON table_name;

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

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

/* Oracle */
DROP INDEX index_name;

SQL DROP INDEX Example

Consider a table called Employee which contains an index named idx_name. To drop this index, the following statement can be used:

/* MySQL / MS Access / SQL Server */
DROP INDEX idx_name ON Employee;

/* MySQL / SQL Server */
ALTER TABLE Employee
DROP INDEX idx_name;

/* SQL Server */
DROP INDEX Employee.idx_name;

/* Oracle */
DROP INDEX idx_name;