Hands-On-Lab: SQL Server Clustered Index Internals

Hello Friends,

Here is another popular lab from Hands-On-Lab library: SQL Server Clustered Index Internals.

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

SQLMaestros Hands-On-Labs enables the practical way of learning. Each lab document consists of multiple exercises and each exercise consists of multiple tasks. Apart from step-by-step instructions, the HOL document contains images, explanations, observations, notes, tips & tricks to ensure a great learning experience. With each lab, you can practice a specific concept step-by-step. With our HOL documents, you will get hands-on with hundreds of topics in SQL Server & Microsoft Data Platform. Learn More.