SQL INDEX

The Index in SQL is a special table used to speed up the searching of the data in the database tables. It also retrieves a vast amount of data from the tables frequently. The INDEX requires its own space in the hard disk.

The index concept in SQL is same as the index concept in the novel or a book.

It is the best SQL technique for improving the performance of queries. The drawback of using indexes is that they slow down the execution time of UPDATE and INSERT statements. But they have one advantage also as they speed up the execution time of SELECT and WHERE statements.

In SQL, an Index is created on the fields of the tables. We can easily build one or more indexes on a table. The creation and deletion of the Index do not affect the data of the database.

In this article, you will learn how to create, alter, and remove an index in the SQL database.

Why SQL Index?

The following reasons tell why Index is necessary in SQL:

• SQL Indexes can search the information of the large database quickly.
• This concept is a quick process for those columns, including different values.
• This data structure sorts the data values of columns (fields) either in ascending or descending order. And then, it assigns the entry for each value.
• Each Index table contains only two columns. The first column is row_id, and the other is indexed-column.
• When indexes are used with smaller tables, the performance of the index may not be recognized.

Create an INDEX

In SQL, we can easily create the Index using the following CREATE Statement:

CREATE INDEX Index_Name ON Table_Name ( Column_Name);  

Here, Index_Name is the name of that index that we want to create, and Table_Name is the name of the table on which the index is to be created. The Column_Name represents the name of the column on which index is to be applied.

If we want to create an index on the combination of two or more columns, then the following syntax can be used in SQL:

CREATE INDEX Index_Name ON Table_Name ( column_name1, column_name2, ...., column_nameN);  

Example for creating an Index in SQL:

Let’s take an Employee table:

Emp_Id	Emp_Name	Emp_Salary	Emp_City	Emp_State
1001	Akshay	20000	Noida	U.P
1002	Ram	35000	Jaipur	Rajasthan
1003	Shyam	25000	Gurgaon	Haryana
1004	Yatin	30000	Lucknow	U.P

The following SQL query creates an Index ‘Index_state’ on the Emp_State column of the Employee table.

CREATE INDEX index_state ON Employee (Emp_State);  

Suppose we want to create an index on the combination of the Emp_city and the Emp_State column of the above Employee table. For this, we have to use the following query:

CREATE INDEX index_city_State ON Employee (Emp_City, Emp_State);  

Create UNIQUE INDEX

Unique Index is the same as the Primary key in SQL. The unique index does not allow selecting those columns which contain duplicate values.

This index is the best way to maintain the data integrity of the SQL tables.

Syntax for creating the Unique Index is as follows:

CREATE UNIQUE INDEX Index_Name ON Table_Name ( Column_Name);  

Example for creating a Unique Index in SQL:

Let’s take the above Employee table. The following SQL query creates the unique index index_salary on the Emp_Salary column of the Employee table.

CREATE UNIQUE INDEX index_salary ON Employee (Emp_Salary);  

Rename an INDEX

We can easily rename the index of the table in the relational database using the ALTER command.

Syntax:

ALTER INDEX old_Index_Name RENAME TO new_Index_Name;  

Example for Renaming the Index in SQL:

The following SQL query renames the index ‘index_Salary’ to ‘index_Employee_Salary’ of the above Employee table:

ALTER INDEX index_Salary RENAME TO index_Employee_Salary;  

Remove an INDEX

An Index of the table can be easily removed from the SQL database using the DROP command. If you want to delete an index from the data dictionary, you must be the owner of the database or have the privileges for removing it.

Syntaxes for Removing an Index in relational databases are as follows:

In Oracle database:

DROP INDEX Index_Name;  

In MySQL database:

ALTER TABLE Table_Name DROP INDEX Index_Name;  

In Ms-Access database:

DROP INDEX Index_Name ON Table_Name;  

In SQL Server Database:

DROP INDEX Table_Name.Index_Name;  

Example for removing an Index in SQL:

Suppose we want to remove the above ‘index_Salary’ from the SQL database. For this, we have to use the following SQL query:

DROP INDEX index_salary;  

Alter an INDEX

An index of the table can be easily modified in the relational database using the ALTER command.

The basic syntax for modifying the Index in SQL is as follows:

ALTER INDEX Index_Name ON Table_Name REBUILD;  

When should INDEXES not be used in SQL?

The Indexes should not be used in SQL in the following cases or situations:

• SQL Indexes can be avoided when the size of the table is small.
• When the table needs to be updated frequently.
• Indexed should not be used on those cases when the column of a table contains a large number of NULL values.