Know When To Use Non-Clustered Index In SQL Server Database

SQL server is a relational database management system developed by Microsoft and it is used to manage and store data. A database is a collection of information and all the data in the database stored in the tabular form. The users can create an index on each table to retrieve data rows from the table quickly. In SQL server, an index is of two type- Clustered index and non-clustered index.

A clustered index stores and sort the rows in the table based on their key values. These key values are stored in a B-tree structure that helps the SQL Server to search the rows associated with the key values efficiently and quickly.

A non-clustered index has a separate structure from the actual data rows of the table. A non-clustered index has the index key values and each key-value entry contains a pointer to the row that has the key value. It is just like a “table of content” of a book.

The use of the non-clustered index in SQL server is a better choice than clustered index because clustered index stores and sort data in the physical order and a table can have only one clustered index. On the other hand, non-clustered index stores data in logical order and a table can have multiple non-clustered indexes. Therefore, in this article, we are going to discuss “When to use the non-clustered index in SQL server”.

Difference Between Clustered And Non-Clustered Indexes

S.No Clustered Non-Clustered
1. Clustered index sorts the records and stores them physically according to the order. Non-clustered index creates a logical order for data rows and uses pointers to physical data files.
2. The clustered index physically sort all data rows. The non-clustered index does not sort rows physically.
3. There can be only one clustered index on the table. There can be multiple non- clustered indexes on the table.
4. It stores data pages in the leaf nodes of the index. It does not store data pages in the leaf nodes of the index.
5. The size of the clustered index is large. The size of the non-clustered index is smaller than the clustered index.

Disadvantages Of Clustered Index

  • The clustered index takes a long time to update records when the fields in the clustered index are changed.
  • Clustered index sorts all data rows physically. Therefore, it takes a long time to search for the required data.
  • The leaf nodes contain data pages in the clustered index.
  • A table contains only one clustered index.
  • Insert and update operations take a huge amount of time in the clustered index.

When to use the non-clustered index in SQL server

The users can create a non-clustered index in SQL server instead of the clustered index in various conditions. Some of them are listed as below:

  • Large Index Key Size: When the size of the index key is large then create a non-clustered index on the table.
  • Retrieve Data Quickly: To retrieve data from the table quickly use the non-clustered index in the SQL server.
  • Avoid Overhead Cost: To avoid the overhead cost associated with clustered index creates a non-clustered index on the table.
  • Avoid Physical Sorting: A non-clustered index stores data in logical order and does not sort data rows physically.
  • For Fast Insert And Update: The insert and update operation is faster in the non-clustered index.
  • To Create Multiple Index: A table can have multiple non-clustered indexes in SQL server. Therefore, it can be used to create more than one index.

Conclusion

Non-cluster index stores data in logical order and does not sort data rows physically. It retrieves data quickly from the table. Therefore, we can use non-cluster to retrieve data quickly, to create more than one index, to avoid overhead cost, to prevent the physical sorting of data rows and when the index key size is large.

You might also like More from author