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.

|–Table Scan(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

An index seek is where SQL server uses the b-tree structure of the index to seek directly to 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.