Question: What is the difference between Index Seek and Index Scan?
Answer: Index Scan retrieves all the rows from the 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.
Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan in the execution plan.
Here are few other related articles on this subject which you may find useful:
- SQL SERVER – Index Seek Vs. Index Scan (Table Scan)
- SQL SERVER – Primary Key and NonClustered Index in Simple Words
- SQL SERVER – Query to Find Duplicate Indexes – Script to Find Redundant Indexes
- SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script
- SQL SERVER – Fundamentals of Columnstore Index
Reference: Pinal Dave (https://blog.sqlauthority.com)
