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


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.


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


About c# and .NET Interview questions

This blog is for developers who want to crack .NET and C# interviews. It has all tips and tricks needed to crack .NET interviews , C# interview , SQL Server interview , Java interview , WCF Interview , Silverlight interview , WPF interview , LINQ interview , Entity framework Interview. Do not forget to watch our Learn step by step video series. Learn MVC in 16 hours:- https://www.youtube.com/watch?v=Lp7nSImO5vk Learn AngularJS Step by Step:- https://www.youtube.com/watch?v=0kmdjqgO9IY Learn Design Pattern in 8 hours:- https://www.youtube.com/watch?v=YDobmucohqk Learn C# and .NET in 60 days:- https://www.youtube.com/watch?v=yh2SrzCkNQA Learn MSBI in 32 hours:- https://www.youtube.com/watch?v=mGPJx3ocFgg Learn SharePoint Step by Step in 8 hours:- https://youtu.be/C2fW76SwJNU
This entry was posted in Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s