cindi cindi -4 years ago 90
SQL Question

SQL Server Plans : difference between Index Scan / Index Seek

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 Source

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 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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download