There are basically two types of indexes:-
√ Clustered Indexes.
√ Non-Clustered Indexes.
Ok every thing is same for both the indexes i.e. it uses “B-TREE” for searching data. But the main difference is the way it stores physical data. If you remember the previous figure (give figure number here) there where leaf level and non-leaf level. Leaf level holds the key which is used to identify the record. And non-leaf level actually point to the leaf level.
In clustered index the non-leaf level actually points to the actual data.
In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data.
So here’s what the main difference is in clustered and non-clustered , in clustered when we reach the leaf nodes we are on the actual data. In non-clustered indexes we get a pointer, which then points to the actual data.
So after the above fundamentals following are the basic differences between them:-
√ Also note in clustered index actual data as to be sorted in same way as the clustered indexes are. While in non-clustered indexes as we have pointers which is logical arrangement we do need this compulsion.
√ So we can have only one clustered index on a table as we can have only one physical order while we can have more than one non-clustered indexes.
If we make non-clustered index on a table which has clustered indexes, how does the architecture change?
The only change is that the leaf node point to clustered index key. Using this clustered index key can then be used to finally locate the actual data. So the difference is that leaf node has pointers while in the next half it has clustered keys. So if we create non-clustered index on a table which has clustered index it tries to use the clustered index.
No comments:
Post a Comment