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:
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:
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.
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 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:
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.
Rename an INDEX
We can easily rename the index of the table in the relational database using the ALTER command.
Syntax:
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:
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:
In MySQL database:
In Ms-Access database:
In SQL Server Database:
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:
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:
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.