Search This Blog

Tuesday, July 31, 2012

Difference between Index Seek vs. Index Scan


What is the Difference between Index Seek vs. Index Scan?

Index Scan:
An index scan means that SQL Server reads all the rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all the rows of the index are examined instead of the table directly. This is sometimes compared to a table scan, in which all the table data is read directly.

Index Seek:
 An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; in terms of performance, this is highly beneficial when a table has a very large number of rows.

1 comment :

Unknown said...

SQL Server uses indexes just like you use the book index to retrieve a subject. Say you need to read about subject X in a book, you will simply go the index and search for the number of pages where X is mentioned. SQL Server does the same thing; it uses the index as a reference to retrieve data from a certain table.