SQL-Index Constraint

CREATE INDEX Constraint

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

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (columnname1, columnname2, ...);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (columnname1, columnname2, ...);

CREATE INDEX Example

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

Delete An INDEX in a Table

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;