What are Indexes in SQL Database? What is the advantage and disadvantage of Indexes?
Indexes are schema objects in a SQL database that can boost query performance while executing queries with a filter condition using where clause.
Indexes are used to retrieve data from databases very fast. An Index is a quick lookup table that is used to find a record that user search very frequently. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements.
Advantage of Indexes
- Searching for Records
The index can help in searching and finding that specific row quickly which ultimately improves the performance of the query. SELECT, UPDATE, and DELETE can get benefitted from the Indexes.
- Sorting Rows
Indexes help in sorting the column used in the Index. Thus, it is helpful for executing the ORDER BY clause which will sort the data in ascending and descending order.
- Grouping Record
Indexes help in the group by the operation when the column used in the group by clause is indexed.
- Maintaining a unique column
Columns that require unique values such as primary key should have a unique index applied. If a column is set as the primary key then a unique index will be automatically applied.
Disadvantage of Indexes
- Additional Disk Space Required
Clustered Column Index does not require additional disk space but Non-Clustered Column Index requires additional space as it is stored as a separate object in the database.
- Insert, Update and Delete Operation become Slower
Whenever there is an insert, update, and delete operation performed on the main table then indexes are also updated which cause increases the execution time of the DML command.
Types of Indexes
Indexes are really useful for big databases as they help in improving search performance.
There are two types of indexes.
- Clustered Column Index
- Non Clustered Column Index
1. Clustered Column Index
Clustered Index determines the physical order of data in a table. If clustered column index is not defined then the primary key column creates clustered column index automatically.
If indexes are set upon multiple columns, then it is known as Composite Clustered Column Index.
Clustered column index does not consume extra space. There can be only single clustered column index. Clustered column index is faster.
2. Non Clustered Column Index
A non-Clustered Index is like an index of a book. Index in a book is placed separately similarly non clustered index is separate from the table storage location.
A table can have more than one non-clustered index, just like a book can have an index at the beginning and at the end of the book.
Non Clustered Column Index take additional space in the database. There can be multiple non clustered column index. They are slower in comparison to clustered column index.
Clustered Vs Non Clustered Index
| Clustered Column Index | Non Clustered Column Index |
|---|---|
| Clustered Column Index does physical sorting of all rows. | Non Clustered Column Index does not does physical sorting. |
| Only Single Clustered column Index can be created. | Multiple non clustered column index can be created. |
| Clustered Column Index does not consume additional space in the database. | Non Clustered Column Index consumes space in the database. It is required to store index separately. |
| Clustered Column Index are faster. | Non Clustered Column Index are slower than Clustered Column Index. |
| A Clustered Column Index can improve performance of data retrieval. | It should be created on columns used in frequently in joins , where and order by clause. |
Conclusion: Optimization
Indexes are extremely important for improving the performance of the query. Also, we should make sure that we optimize our query as per the index created in the database for better performance.
Let suppose the index is set on columns the Last name and First name in the order as mentioned. So while writing a query we have to make sure that the filter is used in the same order to getting the best results of the indexes else the query will not be an optimized query and we will not get the best results.
