Mar 30, 2009

Database Indexes

Mar 30, 2009

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.

2 comments:

  1. Good work Lav !!!
    Your crsip technical writeup are so good for those who left programming some time back. Keep adding all your tech findings !!!

    Thanks,
    Diwakar (from Wipro)

    ReplyDelete

Top Ad 728x90