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 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, ...);
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);
The DROP INDEX statement is used to delete a index on a table.
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;