In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row 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 at each level of the index are linked in a doubly-linked list. In the first exercise we will observe the B – Tree structure of a clustered index. In the second exercise we will understand the concept of page split and how the page split causes fragmentation in case of the clustered index. In the exercises we will explore the significance of choosing proper clustered index key and in the fourth exercise we will look at the ordering of data in case of the clustered index.
Exercise 1: Understanding Clustered Index B-Tree Structure
Exercise 2: Page Splits and Fragmentation in Clustered Index
Exercise 3: Clustered Index Key