SQL – INDEX

Prev     Next

  • SQL INDEX is used to find and retrieve data fast in a table.
  • It is like a pointer or index of a book. So, when we want to search some data in a table, Index searches very quickly without searching the whole table which improves query performance especially in large tables.
  • Indexes need to be created in tables for the columns which are used very frequently for searching (i.e. in WHERE clause)

SQL – CREATE INDEX:

There are 4 types of indexes. There are,

  1. Single column index
  2. Unique index
  3. Composite index
  4. Implicit index

1. Single column index:

  • The single column index is created on only one column of a table.
  • This type of index allows to insert duplicate records in the table

Syntax:

CREATE INDEX index_name
ON table_name (column_name);

2. Unique index:

  • Unique index is created on only one column of a table.
  • This type of index does not allow to insert duplicate records in the table

Syntax:


CREATE UNIQUE INDEX index_name
ON table_name (column_name);

3. Composite index:

  • Composite index is created on two or more columns of a table.

Syntax:

CREATE INDEX index_name
ON table_name (column_name1, column_name2);

Implicit index:

  • Implicit indexes are created by default by the database server on primary and unique key constraints when a table object is created.

4. SQL – DROP INDEX:

An existing table index can be deleted using DROP INDEX statement.


Syntax:

  • In MS Access database: DROP INDEX index_name ON table_name;
  • In Oracle/DB2: DROP INDEX index_name;
  • In MySQL: ALTER TABLE table_name DROP INDEX index_name;
  • In MS SQL server: DROP INDEX table_name.index_name;

Prev     Next

Share this website !!!
Facebook Twitter Linkedin