Difference between index seek and scan in SQL Server…

If you are involved in query tuning, most likely you use execution plan of SQL Server heavily. In those plans, typically we can find two types of index related operations... index scan or index seek. Ever wondered what are the differences between them and which one is better for performance of your query?

Well, let us dive to-the-point here:

Index Scan:  Index scan scans all rows of the table (via the index leaf) to match the given condition.  Index scan happens when Query Optimizer unable to find a useful index to locate the particular record and determines Index scan will be more efficient than a table scan. Since it touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate. However as the table grows (and the percentage of qualified matching rows reduced), this becomes less and less efficient.

Index Seek: Index seek touches only the row that qualify and pages that contain those qualifying rows.  An Index Seek means that the Query Optimizer was able to find a useful index in order to locate the appropriate records. Associate cost of this operation is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table. So this is much better if you have a large table and percentage of qualified matching rows is less.

Conclusion: In general query optimizer tries to use a Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or if it determines a scan will be more efficient (typically true for small tables where most of the rows are matching with given criteria), then it picks Index Scan (After it decides it will be more beneficial than a table scan). If you need to change this choice of query optimizer, you can try to review the indexing strategy and/or you may need to rewrite the query (particularly the search condition). However, please note that both index seek or scan can be best option depending on your situation (tables, matching rowset etc) and you should not try to influence the decision of query optimizer unless you actively attempting to change performance of that particular part of the query.