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.
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.
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)
Hi, Pinal, great post! You mentioned “50 percent or 90 percent”. Is it some setting on server or optimizer decides for it self when to use scan?
if Month function not found date format it will return value is 1, the base month
please explain what is difference b/n table scan,index scan,index seek?is table scan and index scan are same?
Can you please give some detail about identity fields