Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 558
cindi Avatar asked Jul 16 '09 09:07

cindi


People also ask

What is the difference between index scan and index seek?

Explanation. An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records. A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query.

What is the difference between table scan and index scan in SQL Server?

Table scan means iterate over all table rows. Index scan means iterate over all index items, when item index meets search condition, table row is retrived through index. Usualy index scan is less expensive than a table scan because index is more flat than a table.

Should a seek or scan be used against a given table?

Index Seek retrieves selective rows from the table. Index Scan: Since a scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Is index scan better than table scan?

3) index scan is faster than a table scan because they look at sorted data and query optimizers know when to stop and look for another range. 4) index seek is the fastest way to retrieve data and it comes into the picture when your search criterion is very specific.


1 Answers

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.
like image 185
Justin Avatar answered Oct 12 '22 08:10

Justin