SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER - Index Seek Vs. Index Scan (Table Scan) indexfinger 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 the 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 (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that the optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table, then SQL Server has to scan all the records that satisfy the query condition.

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

, , ,
Previous Post
SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By
Next Post
SQL SERVER – 2005 Best Practices Analyzer (February 2007 CTP)

Related Posts

107 Comments. Leave new

  • how tune any query

    Reply
  • hi Pinal ,

    i have 10 ten queries on three tables with 10 where conditions on one table.in that 10 ten queries 3 queries uses
    where condition below

    where city= and state=? and country=?

    how indexes i need to create on these table(one or more).

    this table getting truncate abd loaded everyday.

    Please advise.

    Reply
    • Marko Parkkola
      April 27, 2010 11:06 am

      Hi,

      Have you tried Sql Server Database Tuning Advisor? It gives you nice list of indexes it thinks would be good. You can even tell it to suggest indexed views.

      It’s hard to say which columns you should index just by your where-statement. You could try to index all of those three columns, index per column. After that check Execution Plan to see if indexes are being used or not.

      Reply
  • Hi Kumar,

    I think you can create a composite Index on the three columns instead of creating indexes on each and every column to reduce space and to optimize the query performance. Correct me any body if i am wrong.

    Reply
    • Marko Parkkola
      April 30, 2010 8:57 pm

      Yes, you can. But if the columns aren’t selective enough you risk that your composite index isn’t used at all. Also the order of columns inside the index is significant. You have to test and test and test again to see if the index is used or not.

      I would suggest you create the index and check execution plan to see if it’s used. Then wait couple of days so that database can collect enough statistics and check it again. Finally sys.dm_db_index_physical_stats function tells a lot what’s going on with your indexes.

      For example I created four composite index in a single table on one of your databases. All of them were used initially but eventually two of them was rendered useless so I dropped them and DB is running like charm.

      Reply
  • Why would sql suddenly change from an index seek configuration to an index scan configuration? We went from screaming fast to no go in one afternoon. What would trigger the change? Since there were no changes made to the database other than additional data, what would automatically trigger this change in sql?

    Reply
  • Hi Pinal,
    I have a base view on top of which there are several views.
    One of the views which is based on the base view gives timeout errors.

    In the execution plan it shows one index seek takes Cost 80%

    There are appropriate indexes on the tables.
    Can you suggest any more optimization.

    Reply
  • Normally I go through your articles and I find it very useful. Could you please suggest when to use clustered index and when to use non-clustered index. What columns should be used for non-clustered index and what columns should be used for clustered index.

    Reply
  • dear sir,

    i am trying to get the i’th row record from the table in sql server 2000,
    for example i give the query
    select * from table_name where row_position=1

    if it is possible to get 1’th record from the table?

    Reply
  • i want to print number of days the student remain absent in aschool daywise(like sun mon tu……) in single row from a student table

    [email removed]

    Reply
    • You need to have a calender table which joins with your original table and calculate the absents

      select datename(weekday,c.date),count(*) from calender_table as c left join your_table as t
      on c.datecol=t.datecol where t.datecol is null

      Reply
  • i have a table with 45000 rows with a column of amount.
    i want to create index on it but with conditions.like i want to create an index for amount>98000 rows , so that when i search for amount >98000 , then it do not searches the whole 45,000 rows but only searches the index with amount>98000.
    how do i do this?

    Reply
  • good

    Reply
  • Hi Pinal,

    I have a table which has 5000 rows of data in it. When i try to use a simple query “Select * From Table” its taking 20 seconds of time. I did not use any where conditions in my query. Its a just “Select * From Table”. I have different data type columns in my table including bit datatypes. I dont know how to improve the performance of my query. Please, help me in this.

    Reply
  • Did you try “Select * From Table with(nolock)” ?

    Reply
  • I have a table with M pages .IIf i want to retreive its data without index .What will be the cost of processing – full table one row retireval,full table one column retrieval,subset of table one row retrieval….
    If I use B+ index can i improve the query processing…Please answer urgently I am in dire need of this answer

    Reply
  • how does an index seek knows “qualifying rows”? does it scan that before-hand ?

    Reply
  • Hai sir,

    Thanks for the info.

    How can a index scan converts to index seek?

    Reply
  • Hi sir,
    Thanks for the info
    how to reduce table scan where condition in sql server 2005

    Reply
  • Ashok Kumar J
    June 19, 2012 12:36 pm

    hi pinal dave, i am your deepest fan in the world. i have question, could you clear me or suggest me……………how to reduce clustered index delete cost? how to convert index seek to clustered index seek ? how to convert predicate to seek predicate………..kindly give me suggestion……….highly appreciated

    Reply
  • Ashok Kumar J
    June 19, 2012 12:39 pm

    In Our Production server 32 GB RAM, Last week In C drive total size is 72 GB and
    free space 23 GB Size But This week suddenly 10 GB of resource(disk space) occupied by SQL resources So Now total size is 72 GB and free space is 13 GB only. Is any Query to find which SQL server objects(view/table/sp/triggers etc) consumes this 10 GB of Disk space ? How to sort out in future? any method available ?
    Quick suggestions are highly appreciated………………..

    Note:
    this week no installation or update/delete/insert had done in cpu and sql server.

    Reply
  • Ashok Kumar J
    June 19, 2012 12:41 pm

    Can we use same table column for clustered index and non clustered index? is it useful or not?

    Reply
  • Elmozamil Elamir
    July 4, 2012 6:30 pm

    Thanks a lot it really help me.
    If I have many unused index, (last_user(system)_scan(seek)) is null
    Is it better to remove the index.

    Reply

Leave a Reply

Menu