Indexes are a constant problem in understanding for beginners (and the "not so beginners") in the database world. And don't you just love the
hardcore mathematical explanation of B-Trees and their traversal. Personaly I much rather have visual props and a story to support an explanation.
Appealing visual props are even better. That's how this post originated. So let us begin!
Imagine you live in a pre-computer-in-every-nook-and-crane world (around 1960's :)).
You wake up one morning with a huge craving to read Agatha Christie's books. So you go to the library which has a few million books.
You walk up to the cute librarian (look left for the appealing visual prop :)) and ask her if she can show you to the Agatha Christie
books. She goes to the huge drawer (drawer #1) of little cards which hold just basic info about each book like ISBN number, author and
title and are sorted by ISBN number. She pulls out the first card, looks at its bookshelf id and takes you to the correct bookshelf.
You get your book and are on your merry way home. When you read your book you go back to the library, return
the books and ask the cute librarian (again look left for the appealing visual prop :)) to get you all the books by Agatha Christie written
in the 1951. This time she goes again to the huge drawer of little cards (drawer #2) but these are different than the previous ones. These hold the year of
publishing and the ISBN number of the book. She writes down the ISBN numbers of all books and goes back to the first huge drawer and
looks up cards that match ISBN numbers she found earlier. Again you get your books and are on your merry way home.
You read them return them, get some more books until you muster up the courage to ask the cute librarian out and you live happily ever after.
Nice story isn't it?
"So what the hell am i talking about?" you might ask. Well a library is a very good analogy for indexes. How? Let's review:
library = table
drawer with ISBN Number = Clustered index
drawer with year and ISBN Number = Non-Clustered index
cute librarian = a perk :)
So what you acctually did in this story is this:
You executed a query (i want some books with a condition) and a query processor (cute librarian) performed a Clustered index
seek on the first book hunt and a Non-Clustered index seek on the second book hunt.
It produced a shortest plan for the desired action (go to the bookshelf and return with the books) and it retured the desired data back (your books).
On both times she had to return a full row (a book) of data by locating it in the Clustered index (looking in the Drawer #1).
This operation is also known as a Bookmark Lookup and is the most expensive operation you can do with indexes short of index scan.
And how do those huge card drawers translate to clustered and non clustered index?
ISBN Number is the column on which the clustered index is defined. The books on the shelves are sorted accordingly. The bookshelf numbers
are pages of data in the table. The second drawer contains cards with year of publisihing and an ISBN Number. Year of publisihing is a Non-Clustrered index.
Each non-clustered index is connected to a Clustered index, thus the ISBN Number on the second drawer's cards.
You can see that as long as the ISBN Number is contained in the Clustered index the non clustered won't be changed.
This means that reindexing (not rebuilding) the Clustered Index won't rebuild the non clustered index as long as ISBN Number stays in
the Clustered Index.
But admit it! You don't care about indexes, you just care about the cute librarian :)
