Before going to see the difference between cluster index and noncluster index. Please go through Cluster index and Noncluster index article which I have described before, then only you can able to understand the difference.
| Cluster Index | NonCluster Index |
| Cluster Index will sort and stored the data in Physical table itself | NonCluster index also sort the data, but it will not store the data. |
| Index will be created based on B-Tree | NonCluster Index also created on based on B-Tree. |
| Cluster Index will not depend on NonClsuter index | But NonCluster Index depend on Cluster index when we are selecting multiple column from the table, but we have only one NonCluster index in the table |
| Actual data will be stored in Leaf node | NonCluster will hold the only Cluster index key to refer the actual data. |
| We can create only one Cluster index | But we can create multiple NonCluster Index. |
| Cluster index will not take any addition space to store the data. | NonCluster Index will take additional space to hold the cluster index key |
| Cluster index faster than NonCluster index, because no need addition lookup to pull the data. Have Look below Example 1 | Noncluster need addition looup to pull data based on Cluster index mapping, So Noncluster slower than Cluster index Have Look below Example1 |
Below are some of detail difference between Cluster and NonCluster Index.
Example : 1
I m running the query to pull the Std_Name, Age based on std_id column which is have cluster index and see the execution plan.
The SQL Optimizer will do index seek operation to pull the two column data which do Key lookup operation.
Now we will run the same query but based on Mobilenumber filter which column have NonCluster index. You can see the different type of operation will do SQL Optimizer.
SQL Optimizer will take addition lookup to search the Std_Name, Age from target table based on Cluster Index Key, So SQL take addition operation to pull the data, and its impact performance when you compare with Cluster Index.
Thank you..!!
Leave a Reply