Can you explain and show how indexes make search faster?

Answer:

 

SQL Server Indexes are favorites of interviewers when it comes to SQL Server interviews and .NET interviews. One of the basic questions which interviewers ask is “why do we need indexes?” and our excited developer friend shouts “PERFORMANCE”.

Then the smart interviewer drills down more asking further how does it increase performance and there’s a big “SILENCE” :-).

In this question let’s demonstrate practically how indexes improve performance and why.

Assume that we have the following “Customer” table.



Let’s first see an example for the above table without creating an index on it and let’s look how much time the SQL engine takes to fetch the required data.





You can see from the above image that there are no indexes.

In order to view what exact time does the SQL engine takes to retrieve the requested data, you need to set the “set statistics io on” in your query.

Query: - set statistics io on Select * from Customer where Customer.CustomerID = 1034

When you execute your query the result set will be shown along with the Message like below diagram.




In the above diagram you can see that the Logical reads is 17

Now let’s see an example for the same table but with “Index” created on it and let’s see the difference between the logical reads value.

When we create an index on any column of a table then data gets divided like the following B-Tree diagram, so that search becomes easier and faster. For example if we want to search 1500 it will straight go to the section 1001 – 1500 rather than looping though all records.





To create index on table follow the below steps




Query: - Set statistics io on Select * from Customer where Customer.CustomerID = 1034

The result set will look by below diagram.



In above diagram you can see that now the “Logical read is 11” as compared to the result set of the table without index which was “Logical read 17”.

This shows that the indexes make search faster because it uses B-Tree structure to search the required result set.

One of the other ways to increase performance is by using SQL Server profiler and index tuning wizard.