INDEX in SQL

In this tutorial, We will learn about the INDEX in SQL.

Indexes allow the database application to find data fast; without reading the whole table.

A Unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraints in a table definition or user can create Index manually to speed up the access to the Rows.

 

SQL Syntax :

SQL > CREATE INDEX index_name
ON table_name (column_name) ;

 

Create INDEX in SQL with Example

In the below example, We will create the INDEX on table employee.

SQL> Create INDEX emp_lastname_inx

           ON employees (last_name);

 

INDEX Creation Guidelines

Index creation is beneficial when –

  • A Column contains a wide range of Values.

  • A Column contains a large number of NULL values.

  • One or more columns are frequently used together in WHERE clause or JOIN condition.

 

Index creation is not beneficial when –

  • The columns are not often used as the condition in the query.

  • The table is updated frequently.

  • The Indexed columns are referenced as part of Expression.

 

DROP INDEX in SQL

We drop the INDEX using DROP INDEX in SQL.

 

Syntax : DROP INDEX index_name;

SQL > DROP INDEX emp_lastname_inx ;

 

0 Comment

Leave a Reply