.NET and SQL Server Interview Question - How does index makes search faster?

Answer:

For better understanding, let us consider a simple search example which shows differences between a table, declared with index and without index.
Let's first see an example for a table which is created without declaring an index and look how exactly the SQL search engine will perform action. Below diagram will give u better idea…

In the above example, SQL search engine will search from initial till it finds the respective record and once the record is found it will basically display the record. Further, When we create an index on any column of a table then the large data get divided like following B-Tree diagram, so that search becomes easier and faster.


B-tree structure of a SQL Server index

For Example-

Suppose we have to search value 25 in an indexed column, the query engine will first look in the “Root Node” to determine which node to refer in the “Branch Nodes”. In the above example first “Branch Node” has Value 1 to 20 and the second “Branch Node” has Value 21 to 40, so the query engine will go to the second “Branch Node” and will skip the first “Branch Node” as we have to search Value 25. Same like “Branch Nodes” the query engine will operate the “Leaf Node” to retrieve respected result.

Regards,

Please click here to see more .NET and SQL Server interview questions