SQL Server Index and its Types

SQL Server Index

Index is database object which is used to search and retrieve data from a table or view in faster way. Index was introduced in SQL Server 2012. Without indexing, we have to use table scan to search a data in table which can be very time consuming if there is huge number of rows in the table.

Indexes are created on one or more columns of a table and are used to improve the speed of data retrieval operations. Index is useful for query in which column of a table is searched to find a value. The index on the column improves the performance of the query if the number of rows in the searched table is very large. Index should be avoided if the number of rows in the searched table is small.

Types of Index

There are many types of indexes in SQL Server. They are as follows:

• Clustered Index
• Non-Clustered Index
• Unique Index
• Filtered Index
• Covering Index

Clustered Index: A table can have only one clustered index. When a clustered index is created on a table, the rows in the table are physically rearranged to match the order of the index. The column on which index is applied is called indexed column. There can be only one indexed column. Clustered index is automatically created on primary key column constraint column of table by SQL Server. Click  here for its details.

Non-Clustered Index: Non-clustered index is created separate from the table for which index is created. Therefore, a table can have many non- clustered indexes. The physical order of rows of table is not affected due to non-clustered index. Non-clustered indexes can be created on different columns of a table. There can be many indexed columns. Click here for its details.

Unique Index: It is similar to a non-clustered index, but it ensures that the indexed columns contain unique values, preventing duplicate entries in the indexed columns. Click here for its details.

Filtered Index: An index created on a subset of data in a table, based on a filter condition. It includes only the rows that meet the specified criteria. Click here for its details.

Covering Index: An index that includes all the columns required to satisfy a query, allowing the database engine to fulfill the query without accessing the actual table is called covering index. Click here for its details.

Cost of Index

Index is created to facilitate the search operation on table. But additional resource is required to create and maintain the index on table. Obviously there is a trade-off between improved query performance and the overhead of maintaining the index during data modifications (inserts, updates, and deletes). Therefore, it's essential to carefully choose which columns to index based on the queries executed against the table and the overall workload of the database.

In the next post we will learn about Clustered Index in SQL Server.