21

I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page Allocations. I really don't know what it means, or if one is better than the other.

If someone could give a simple definition of each, and also explain why Leaf or Non-leaf matters, it would be appreciated!

Thomas Stringer
  • 42,434
  • 9
  • 120
  • 155
meltdownmonk
  • 377
  • 1
  • 3
  • 8

2 Answers2

26

Think about it like this: leaf level pages are the end of the road for the data search through the B-tree structure of an index. They contain the data defined in the index definition (or in the case of a clustered index, all of the table's data) and a row locator to the data row (in the case of a nonclustered index).

Non-leaf level pages contain the "road map" to the leaf level pages/data by including the key value as well as a pointer to either another non-leaf level page (depending on the B-tree depth and location of the intermediate page) or the resulting leaf level page (index page for a nonclustered index, and data page for a clustered index).

Edit: Here's a good image to put a picture to the idea.

enter image description here

Thomas Stringer
  • 42,434
  • 9
  • 120
  • 155
4
  • leaf = In a clustered index, where the all the row data data is. In a non-clustered index, the columns making up the index, plus any included columns and the clustered index key (if table has a clustered index)
  • non-leaf = internal index node (contains only the column data that is part of the index)

Every index is comprised of a single root node, possibly several layers of intermediate internal tree nodes and a single layer of leaf nodes.

In a clustered index, the leaf nodes are effectively the table. In a non-clustered index, the leaf nodes contain column data, any included columns and the clustered index keys.

SQL Server Index Basics

Mitch Wheat
  • 1,376
  • 9
  • 17