Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

migrated from stackoverflow.com Mar 16 '13 at 0:11

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 7 down vote accepted

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

share|improve this answer
    
was just checking out your blog: some great info. The powershell tips are great. –  Mitch Wheat Mar 16 '13 at 0:57
1  
Neatly put (+1). –  Mark Storey-Smith Mar 16 '13 at 1:01
    
@MitchWheat Thank you! I love PowerShell :-) –  Thomas Stringer Mar 16 '13 at 1:49
    
@MarkStoreySmith Thanks!!!! –  Thomas Stringer Mar 16 '13 at 1:53
    
I am still slightly confused by some of the terminology, but I'm getting a better grasp of the concept. Thank you for this answer. –  meltdownmonk Mar 21 '13 at 16:43
show 3 more comments
  • 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

share|improve this answer
    
I thought (perhaps wrongly) that tracking of leaf/non-leaf applied to non-clustered indexes also? Your bullet points read as applicable to clustered indexes only. –  Mark Storey-Smith Mar 16 '13 at 0:59
    
will make clearer... –  Mitch Wheat Mar 16 '13 at 1:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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