19

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!

24

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

| improve this answer | |
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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.