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 stores 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 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 non-clustered index in SQL server”.
Difference Between Clustered And Non-Clustered Indexes
|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.||Clustered index physically sort all data rows.||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 clustered index.|
Disadvantages Of Clustered Index
- 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 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 clustered index.
When to use non-clustered index in SQL server
The users can create a non-clustered index in SQL server instead of 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 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 are faster in 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.
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 physical sorting of data rows and when the index key size is large.