SQL Index

Introduction to Index

  • An index is a object which is used to improve performance during retrieval of records.
  • It helps to retrieve the data quickly from the tables.
  • When column contains a large number of NULL values, then we can create Index.
  • It a structure that provides faster access to the rows of a table based on the values of one or more columns.
  • It stores data values and pointers to the rows where those data values occur.
  • If a table is very small, then we cannot create Index.
  • Indexes are very useful and make the data access very fast.
Syntax:
CREATE INDEX index_name ON table_name;

Example

CREATE UNIQUE INDEX emp_ename_index
ON Employee(Ename);


  • In the above example, the UNIQUE keyword is used when combined values of index should be unique. It does not allowed duplicate values to be inserted into the table.
  • We created an Index on Employee name (Ename) column in the Employee table.
  • Indexes can be dropped explicitly using the DROP INDEX command.
DROP INDEX emp_ename_index;