Clustered Index vs Non Clustered Index
The whole point of having indexes is to speed up the search process of returning the requested data.
Let’s say you would have 100 records, if you would need the 73th record, you would have to go through 1–73 sequentially to find the 73th record. This search process is slow. To speed up the the process Rudolf Bayer and Ed McCreight came up with the B-Tree structure in 1971.
When speaking about a B-structure, you can see that the data has 3 layers:
- Root node
- Non-Leaf Nodes
- Leaf Nodes
How do indexes work?
Indexes make the search process faster by using something called B-Tree structure internally:
Non-Leaf Nodes: Indexes between the root node and leaf node
Leaf Nodes: Bottom indexes
Clustered Index vs Non Clustered Index
The big difference is that the leaf node of a clustered index actually contains the data. In a non clustered, the leaf node has a reference to the actual data.
Conclusion
There can only be one clustered index on a table as the index sorts all the data rows physically, Therefor, it takes a bit longer to search for the required data. When the key is large and and you want to increase performance on your read/update statements, a well designed non clustered index can be best fit in those situations.
