Tuesday, September 14, 2004

clustered and nonclustered indexes

Okay, so I finally got a metaphor for the two types of indexes which even a lay person can understand.

You're looking for information on wallabies. You decide to go to the university library. Picture these two scenarios for reference books:

a) You find a book on animals. You flip back to the index at the back, look in W, then Wallaby, then it gives you a page number. Then you flip to the page number and look for the information there.

b) You go to an encyclopedia. No need for an index. Just go to the W book, and turn straight to Wallaby. The information is there.

The former is a non-clustered index, and the latter is a clustered index. Clustered indexes store the actual data in sorted order, so when you follow the sorting path, you actually get to the data itself - no need to "flip" to another page. Non-clustered indexes only contain references to the location of the data, much like a generic reference book's index.

Hope that helps any of you out there like me for whom this isn't inherently obvious....


Post a Comment

<< Home