SQL Server Non-Clustered Index Internals

Description

In this lab, we will clear our understanding on non-clustered index. This lab is divided into multiple exercises and in each exercise, we will cover different aspects of the non-clustered index. In the first exercise, we will look at the B-Tree structure of the non-clustered index, we will also look at pages at a different level of the non-clustered index. In the second exercise, we will look at non-clustered index over a heap and we will also explore RID LOOKUP. In the third exercise, we will look at non-clustered index over a clustered index and the concept of key/bookmark lookup. And in fourth and last exercise of this lab, we will cover a select query to minimize I/O with a non-clustered covering index.

Exercise 1: Understanding Non-Clustered Index B-Tree Structure
Exercise 2: Non-Clustered Index on Heap
Exercise 3: Non-Clustered Index over Clustered Index
Exercise 4: Covering Index