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