SQL 101 - Clustered index vs. Non-clustered

More translating of SQL stuff into less technical terms for the new folks...

SQL Server (and most other database systems) offer the option of using indexes on your data to help queries go faster. The purpose of this post is to give the new SQL dude a quick mental connector while learning the concept.

Clustered Index:

Think of the White pages phone book you have at the house. Now, find the entry that has your phone number in it. Mentally, you think of your last name (parameter 1 in a query), then your first name (parameter 2). So if your last name is Gates, you flip directly to the G section. If your first name is Bill, you go to the B section within the Gates area. At that point, there may be more than one Gates, Bill entry, so your eyes start scanning through them for some other piece of identifiable info, such as a street or city (parameter 3) until you find the correct entry. You then slide over to the right, look at the phone number and return that to your brain.

The White pages are a "Two-column" clustered index on lastname, firstname in alphabetical order (Ascending). The data itself (the names) IS the index. No extra pages at the back of the book. Speaking of extra pages....

Non-clustered index:

Think of every technical book or textbook you've ever read. There is almost always a collection of additional pages at the back of the book called the index. These pages do not contain any of the data about the topic at hand...just pointers to where in the book you can find what you are looking for.

Imagine you are holding a SQL Server 2005 administrators book, and you want to find every reference to "Replication." Yes, you can look in the table of contents but that may leave out an entry found in the Backup/Restore chapter, or Performance troubleshooting. So, you go to the back of the book, look through the alphabetical list of Keywords for "Replication" and now you know that the word exists on pages 45, 255-298, and 453. You have a collection of pointers to the specific data pages in your book that may contain the information you need.

What if I don't have any indexes?

No clustered index: Imagine finding your name in a white pages that was not sorted alphabetically. You would have to start at the first entry and read every single row until you hit it (a table scan in SQL speak). Ugh.

No non-clustered index: Imagine me telling you to find the phrase "SQL Profiler" in that SQL book you bought, after I rip the table of contents and index pages out. Sounds like a fraternity hazing ritual for IT geeks ;)

How many can I have?

Clustered: 1. How many ways can you sort and print the data in the SAME book? 1.

Non-clustered: More than one, depending on the database platform and version.

That's all for today...what indexes to have is not a 101 level discussion, other than to say...whatever you join or search on is a good candidate.

Now you are ready for part of your interview ;)

Kevin3NF