What is the difference between a table and index scan in an execution plan?

Question

When I create a graphical query execution plan of a query, I notice that there are two types of scans: Table Scans and Index Scans. How are these different?

Answer

When the Query Optimizer is asked to optimize a query and create an execution plan for it, it tries its best to use an Index Seek. An Index Seek means that the Query Optimizer was able to find a useful index in order to locate the appropriate records. As you probably know, indexes make data retrieval in SQL Server very fast.

But when the Query Optimizer is not able to perform an Index Seek, either because there is no indexes or no useful indexes available, then SQL Server has to scan all the records, looking for all the records that meet the requirements of the query.

There are two types of scans the SQL Server can perform. When a Table Scan is performed, all the records in a table are examined, one by one. For large tables, this can take a long time. But for very small tables, a table scan can actually be faster than an Index Seek. So if you see that SQL Server has performed a Table Scan, take a note of how many rows are in the table. If there aren't many, then in this case, a Table Scan is a good thing.

When an Index Scan is performed, all the rows in the leaf level of the index are scanned. What does this mean? Essentially, this means that all of the rows of the table or the index are examined instead of the table directly. Sometimes, the Query Optimizer determines that an Index Scan is more efficient than a Table Scan, so one is performed, although the performance difference between them is generally not much.

You might ask that if there is an index available, why can't an Index Seek be performed? In some cases, such as if a huge quantity of rows need to be returned, it is faster to do an Index Scan than an Index Seek. Or it may be because the index is not selective enough. In any case, the Query Optimizer doesn't think the available index is useful, other than for performing an Index Scan.

So what does all this mean from an analysis standpoint? Generally speaking, an Index Scan or an Index Seek is almost the same thing, from a performance perspective. If you see any one of these in a query execution plan, the first thing you need to do is to see if there are few rows in the table. If so, then a scan is OK. Or, if many rows are being returned, then a scan is often faster than an Index Seek, and the Query Optimizer made the correct choice of selecting a scan. The only way to speed up this particular situation would be to find a way to rewrite the query in order to return fewer rows, assuming this is possible.

If the above two reasons don't apply, then your next step would be to try to identify useable indexes to help speed the performance of the query, assuming that the current performance of the query is unacceptable, so that an Index Seek is performed instead of an Index or Table Scan.