Before I go and answer this question, answer me one thing. Let’s say you have a book and you want to navigate to 6th lesson. What will be your first step? Will you start turning your page one by one till you reach 6th lesson. If yes then you are awesome…no need to learn anything else. If your answer is No then what will you do. I wise person will first use the index of the book and get the page number of lesson and navigate accordingly.
Sql server does the same task. The only goal of using indexes in sql server is increasing search performance. (In select queries)
How search normally performed in sql server?
It is done in sequential manner. It means if you are looking for 10th row you have to manually browse every previous row, asking everyone the same question ”Are you row 10” and everyone reply “No”.
This is called as Table scan.
Anyone can easily say this will affect performance.
How indexes improves search?
Indexes create B-Tree structure or Balanced tree structure internally.
What is B-Tree?
- In B-Tree there will be a root node at the top, leaf node at the bottom and intermediate node at the middle.
- Root node and Intermediate node contain Index rows.Index Row contains 1) Key value 2) pointer to another intermediate row or leaf node.
- Leaf node will point to actual data (applicable only for clustered Index)
How nodes in the B-Tree organized?
How B-Tree makes search faster?
While searching when B-Tree is used lots of records got skipped, because our data will be either at left side or at right side. Ultimately performance get improved.
This kind of searching is called as Index scan.