I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

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!

 

librarianSmall

 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 :)

 

kick it on DotNetKicks.com
 

Print | posted on Tuesday, September 18, 2007 8:15 PM | Filed Under [ SQL Server Back to Basics ]

Feedback

Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Thanks, Mladen, I enjoyed it!

Something missing that you may want to add:

What happens when I ask the librarian "can I see all of the books written by Agatha Christie that contain the word "monkey"?

That gives a good example what has to happen if you ask for something that seems simple, but it is not covered by an index ..... time to open up and look through every page of every Agatha Christie book!
9/18/2007 10:42 PM | Jeff
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

well i wanted to leave something for the sequel again starring the cute librarian :)
9/18/2007 10:46 PM | Mladen
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

hmm... that would work too, not a bad idea.
Thanx Adam!

i did have to look up what the Dewey Decimal System is :)
9/21/2007 10:19 AM | Mladen
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

nicely explained
12/26/2007 1:51 PM | Ajit Singh
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

this is the best explanation ever
1/21/2008 2:50 PM | The Name
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

It was a good explanation.
Thank you
2/12/2008 6:57 AM | Nazia
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Really it was explained by a good exaple.
3/12/2008 10:28 AM | kanak
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

very nice explanation.. Thank you
4/14/2008 11:18 PM | George
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain
the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.


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. Personally I much rather have visual props and a story to support an explanation.
5/8/2008 11:04 AM | Venkat Rama Raju.K
Gravatar

# What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

i thing dat appealing visual prob help a lot in understanding index betr, without feeling bore...... thks
6/11/2008 2:02 PM | Jiju
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Good god man get to the point.

there are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain
the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.


Thaks to the witer of this post I found my answer but for that this page is waste.
7/31/2008 4:02 AM | annon
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

glad you liked it :)
7/31/2008 10:19 AM | Mladen
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

I don't get it !!!
When I went to the cute librarian the first time for Agatha Christie's books why did she find it on the first card she drew out??
10/30/2008 12:53 PM | caisys
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

she wasn't properly optimized! i hope you helped her :))
10/30/2008 1:59 PM | Mladen
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Thans.
Nice Story.
11/3/2008 3:50 PM | Amer
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Thanks for giving proper clarification b/w cluster and non-cluster Indexes.
1/7/2009 11:35 AM | Vikas Manhar
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Thanks man... i easly understood the concept
3/16/2009 11:45 AM | Vivek
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Very nice explanation.
can refer to http://www.dba-oracle.com/oracle_tip_hash_index_cluster_table.htm
4/7/2009 6:01 AM | kumar
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Good story to understand indexes.
6/8/2009 12:17 PM | Ramesh
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

How the result will be processed if there is only Non Clustered index and no Clustered Index created in the table.
9/25/2009 11:33 PM | sa
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

I have readed so many explanations about clustered index but now I understood it.


Thank You
Best Regards
9/26/2009 9:44 AM | Ravikiran D Borse
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

Hi...
i'm really new to database world... and this was one of the best exmple i can get......
neways
thanks a lot
Best regards
10/21/2009 9:32 PM | Ansh
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

TOO GUD

KEEP UP WID THIS Efforts
2/11/2010 10:36 AM | KAMAL
Gravatar

# re: Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

I am the cute librarian!
3/30/2010 3:14 PM | Cute
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET