Interview Question of the Week #021 – Difference Between Index Seek and Index Scan (Table Scan)

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Index
Previous Post
SQL Server – Knowing Deprecated or Discontinued Features Using Extended Events
Next Post
SQL SERVER – CREATE TABLE Failed Because Column ‘C1024’ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns

Related Posts

4 Comments. Leave new

  • Justinas Simanavičius
    May 25, 2015 12:14 pm

    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?

    Reply
  • Paresh Lahade
    May 26, 2015 5:30 pm

    @Paresh Lahade

    if Month function not found date format it will return value is 1, the base month

    Reply
  • please explain what is difference b/n table scan,index scan,index seek?is table scan and index scan are same?

    Reply
  • benali abdelaziz
    September 28, 2016 7:38 pm

    Can you please give some detail about identity fields

    Reply

Leave a Reply