SQL Server - Table (Index) Seeks and Scans

Generally scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. This types of iterators belog to the most fundamental ones that SQL Server supports. They appear in nearly every query plan.

Scan

Scan returns entire table (or index). That means, that scan touches every row in the table whether or not it qualifies. For this reason, tha cost of scan is proportional to the total number of rows in table. Scan is an efficient stratedy if table is small or if most the rows qualify for the predicate.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

If the table is large and if most of the rows do not qualify, we touch many more pages and rows and perform many more I/Os than is necessary.

Seek

Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an 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 no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.