SQL Server Plans : difference between Index Scan / Index Seek

cindi picture cindi · Jul 16, 2009 · Viewed 92.1k times · Source

In a SQL Server Execution plan what is the difference between an Index Scan and an Index Seek

I'm on SQL Server 2005.

Answer

Justin picture Justin · Jul 16, 2009

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records (see http://mattfleming.com/node/192 for an idea on how this works) - time taken is only proportional to the number of matching records.

  • In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
  • Note however that in certain situations an index scan can be faster than an index seek (sometimes significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate.