SQL Server - What is a Clustered Index?

A clustered index sorts and stores the data rows of the table in order based on the clustered index key. The clustered index key is implemented as a B-tree. Each page in a B-tree is called index node. The top of node of the B-tree is called the root node.

The bottom level of the nodes is called the leaf level. Any index level between the root node and the leaf nodes are collectively known as intermediate nodes.  Each page in the intermediate or bottom levels has a pointer to the preceding and subsequent pages, forming a doubly linked list. This structure provides a highly efficient mechanism to speed the process of locating data.

In a clustered index, the root and intermediary nodes contain index pages holding index rows. Each index rows contains a key value and a pointer to either an intermediate level page in the B-tree or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly linked list

Because a clustered index determines the order in which table rows are actually stored, each table can have only one clustered index- the table’s rows cannot stored in more than order.

SQL Server - When SQL Server Performs a Table Scan and Uses and Index?

SQL Server accesses data in one the two ways:

1) Table Scan

• SQL Server reads all table pages.

2) Index

• SQL Server uses index pages to find rows.

By scanning all the data pages in a table called a table scan. When SQL Server performs a table scan, It:

1. Starts at the beginning of the table

2. Scans from page to page through all the rows in the table

3. Extracts the rows that meet the criteria of the query

By using indexes. When SQL Server uses an index, it:

1. Traverses the index tree structure to find rows that the query requests.

2. Extracts only the needed rows that meet the criteria of the query.

SQL Server first determines whether an index exists. Then the query optimizer  -the component responsible for generating the optimal execution plan for a query- determines whether scanning a table or using the index is more efficient for accessing data.

See Also
Clustered Indexes