Mar 30, 2009

Database Indexes

by
I went through a lot of articles to find out the exact differences between clustered and non-clustered indexes.
I have "simplified" the findings, especially for the people (like me) who like to learn things in brief.

Non-Clustered Index
The index in the back of any book is good example of a non-clustered index. Any topic may be easily found in the book by first finding it in the book index and the index will give the page number of the topic.
So, it requires two "look ups" to find a topic. Now in database context we need to first find the position of the index entry and the go to the data page where the rows are present. The leaf node of the B-Tree (data structure index are built on) are the data pages where the rows are stored physically.



Clustered Index
A Phonebook is good example of a clustered index i.e. data and index both are merged together. It requires just one look up to find the data row. Since the data are stored as per the order of the index, there can be only one Clustered Index on a database table.

Top Ad 728x90