Wednesday 15 August 2012
Indexes and B-Tree
Index makes our search easy and faster like Contents Index of Books. So defining indexes to our database will make our search faster.
Fundamentals behind indexing are the use of “B-Tree” or ‘Balanced-Tree” concept. It is 7not a rule that is something is created by SQL Server though is a mathematical derived fundamental.
Fundamentals behind indexing are the use of “B-Tree” or ‘Balanced-Tree” concept. It is 7not a rule that is something is created by SQL Server though is a mathematical derived fundamental.
From this sample diagram we can visualize how B-Tree works so easily and fast. It is better to understand how it actually works i.e. we are searching a number 39.
Ø First start from the first node i.e. root node. It will see that the number is greater than 30, so it moves to the 50 node.
Ø Further in Non-Leaf nodes it compares it more than 40 or less than 40. As it is less than 40 it loops through the leaf nodes which belong to 40 nodes.
We can see that this is all attained in only two steps. It is Faster.
That is how exactly indexes work in SQL Server.
That is how exactly indexes work in SQL Server.
Types of Indexes:
There are basically two types of indexes.
v Clustered Indexes.
v Non-Clustered Indexes.
Everything is same for both the indexes in the context of searching data i.e. it uses “B-TREE”. But the main difference is the way it stores physical data. In B-Tree, there are 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 whereas in Non-Clustered Index the leaf nodes point to pointers (they are ROWID’s) which the point to actual data.
So here lies the main difference is in clustered and non-clustered, in clustered when we reach the leaf nodes we reach the actual data. In non-clustered indexes instead of that we get a pointer which then points to the actual data.
Both the indexes are represented pictorially.
Both the indexes are represented pictorially.
So from the above fundamentals, the basic differences between them as follows:
v 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.
v 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.
