SQL | Clustered Index
An index is a disk-based structure linked to a table or view that facilitates quicker row retrieval.
A table or view’s table or view’s columns are used to create keys in an index.
These keys are kept in a structure (B-tree) that enables SQL Server to quickly and effectively locate the row or rows that correspond to the key values.
Clustered Index
A clustered index is created only when both the following conditions satisfy –
1. The data or file, that you are moving into secondary memory should be in sequential or sorted order.
2. There should be a key value, meaning it can not have repeated values.
Whenever you apply clustered indexing in a table, it will perform sorting in that table only. You can create only one clustered index in a table like a primary key.
A clustered index is as same as a dictionary where the data is arranged in alphabetical order.
In a clustered index, the index contains a pointer to block but not direct data.
Whenever we create an a clustered index, SQL Server will internally create something called Balanced Tree (B-Tree) Structure.
In B-Tree structure, the leaf nodes contain the actual data. So, when we search for any data, the clustered index directly gets the data from the leaf node.
B-tree structure enables us to find the queried rows faster to using the sorted key value(s). Table data can be sorted physically in only one direction for this reason we can define only one clustered index per table.
Root level: The top level of the B-tree is called as root level. The root level is the starting point of the data searching
Intermediate level: This level provides a connection between root and leaf levels. SQL Server does not create an intermediate level when the amount of data rows are too small
Leaf Level: This level is the lowest level of the clustered index and all records are stored at this level
Roll no is a primary key, it will automatically act as a clustered index.
You can have only one clustered index in one table, but you can have one clustered index on multiple columns, and that type of index is called a composite index.
