The CREATE INDEX statement is used to create indexes in tables. The INDEX is used to create and retrieve data from the database very quickly. An Index can be created by using a single or group of columns in a table. When the index is created, it is assigned a ROWID for each row before it sorts out the data.
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 those columns that will use for searching.
The following CREATE TABLE statement defines Employee table.
CREATE TABLE Employee( Eid int NOT NULL, FirstName varchar(100), LastName varchar(100), Emailid varchar(100), Age int, Salary int, Departmentid int );
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name (columnname1, columnname2, ...);
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name ON table_name (columnname1, columnname2, ...);
The SQL statement below creates an index named Employee_index on the FirstName and Salary column in the Employee table
CREATE INDEX Employee_index ON Employee (FirstName, Salary);
The DROP INDEX statement is used to delete an index in a table. Above we used Employee_index name of the index.
DROP INDEX Employee.Employee_index;