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

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 (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 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 query condition.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

92 thoughts on “SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

  1. Than you Nicholas.
    You are correct. Index scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate. That is what I am tring to imply above.

  2. >Index Scan is nothing but scanning on the data pages >from the first page to the last page.

    SO what’s the difference b/w index scan and Table scan ?

    • Hi Pinal,
      Thanks for all you articles they are very useful.
      If Table scan and index scan are the same then why we need to have two terms?
      I am having non clustered index on the table as the selectivity of the query returns more rows optimizer choose for Table scan,eventhough I have index on the table why is that behaviour, my execution plan must show index scan right instead of table scan?

      • A table scan will occur when no index is available on that table. The table then becomes a heap. so if you do a SELECT * FROM without_index you will get a table scan. If you were to put a clustered index on the table you would get a clustered index scan when performing the same query.

  3. Hi,
    Do Index have their own pages, and data rows have their own pages.
    Index scan searches Index pages and table scan searches Data rows pages.

  4. Hi
    It seems from your article that Index Seeks are always desirable.
    If no of Index Seeks are increasing after optimisation, does it mean the query is optimised (to some extent atleast)?

  5. Hi Pinal:
    I have a table with 7 million rows with an index and updated stats.
    When I use a query with in clause, SQL server seems to use index seek upto 70 values in the in clause. Anything more than that it uses index seek.
    I have two questions:
    i) How do I tell it to continue to use seek instead of choosing index scan.
    ii) Is there anything I can do better other than keeping the statistics up-to-date.
    Thanks in advance,

  6. Hi Satinath,

    SQL Server decides based on execution plan either to use Index Seek or Index Scan. User can not specify that. It also depends on how many numbers of records it is retrieving based on how many rows there are in table.

    Following things will help to improve the performance:
    Keep upto date stats.
    Add more index if possible (extra condition in where or join which is indexed)
    Partition Table
    Replication and have read only database.

    Though all the suggestion will take time to implement but that is the next thing to do.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

    • can i partitioned a table which is already used in Merge replication as an article.
      i.e. Table XYZ is replicated using merge replication from server S1 to Server S2. Table is of 700Gb and i want to partitioned the tables.

      Please, let me know how to do it. What is the effect of that on publisher and subscriber in merge replication ?

      Regards.

  7. Hi all,

    From testing, I’ve found that there can be a big difference between an non-clustered index scan versus a table scan or clustered index scan.

    Non-clustered indexes are like mini-tables.
    They just contain the data you’ve indexed in it’s own seperate bucket. So, if all the data you need is in this bucket, you’ll be saving I/Os when you retrieve data from this bucket–versus either a table scan or clustered index scan which contains all the data spread out over more data pages/extents. That’s because you’ll be reading less data; additionally you’ll be reading from data pages that are closer to each other–versus farther away (i.e. reading all the data from each row in the table versus just a few select columns in an index).

    Scans may not ideal. However, non-clustered index scans can save you some serious I/O–versus clustered scan or table scan.

  8. SEEK means that a useful index exists that is helpful for finding a match for the given query. Only data with pointers matching the index need to be retrieved.
    ex index on emp_lname
    select xx,xx where emp_lname=’bhindi singh’
    adv: Less data and fast

    A index SCAN reads all of the pointers of the index to find the match and occurs when an index exists that is only partially helpful. The index might only be partially helpful if there is relatively low selectivity in the distribution of the data. Data that is relatively unique is said to have a higher degree of selectivity whereas data that is less unique has a lower degree of selectivity.
    ex index on emp_lname
    select xx,xx where emp_lname like ‘bhindi%’
    adv: more data and mix and match of search
    disadv: more time

    A TABLE SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.

    select xx,xx where emp_lname like ‘bhindi%’
    select xx,xx where emp_lname = ‘bhindi singh’
    only good for small table with less records.

    In general, SEEKing is more efficient than SCANning; however, a SCAN might be faster than a SEEK if the table is relatively small and cached in memory.

  9. Pinal,

    Is IndexScan as same as TableScan? I am confused by your reply to Lara.

    My understanding is that, in an IndexScan, SQL server reads all pages in the index and only MATCHING rows from data table.

  10. hi pinal,
    I got some difficulties in understanding Selection using indices. It says tht search algorithm tht use an index are:
    -A3(primary index, equality on key)
    -A4(primary index, equality on nonkey)
    -A5(secondary index, equality)

    wht i’m not sure here, is the key…
    wht it means by equality on key(wht it means by key in this context?)- is it refering to sorted datas?

    Pls help me, i hv browse thru few books, but still didnt manage to understand..

    Hope to knw it frm u…

    Thanks.

    Regards,
    Kavi

  11. Pinal,

    Are you sure that an Index Scan is the same as a Table Scan in all cases? If you refer to this MSDN article (http://msdn2.microsoft.com/en-us/library/aa964133.aspx), it makes a differentiation between three different types of lookup:

    * A table scan, where there exist no clustered indices on the table. Offers the worst performance.

    * A clustered index scan, which is confusingly sometimes also referred to as a table scan. Can be faster than a table scan because it may have to load less information.

    * An index seek.

  12. I need a SQL query that will Select a Field name from a Table, compare the contents of that field with a TEXT file, and if there is a match, change the contents of another field associated with that field.

  13. Table 1 – User (IDUser (int) pk, Name (varchar), Female (bit))
    Table 2 – Club (IDClub (int) pk, Name (varchar))
    Table 3 – User_Club (IDUser_Club (int) pk, IDUser Fk, IDClub Fk)

    Goal : To attend all the clubs where the number of female is atleast 10% of entire student population.

    Improve the efficiency of the below sql.

    SELECT [Name] FROM
    Club c
    INNER JOIN
    (
    SELECT c.IDClub, COUNT(uc.IDClub) AS FCount FROM
    User_Club uc INNER JOIN Club c ON uc.IDClub = c.IDClub
    INNER JOIN [User] u ON uc.IDUser = u.IDUser
    GROUP BY c.IDClub, Female
    HAVING Female = 1
    ) tab
    ON c.IDClub = tab.IDClub
    CROSS JOIN
    (
    SELECT CAST(COUNT(*) AS DECIMAL(19,4)) AS UCount FROM [User]
    ) tab2
    WHERE ((tab.FCount / tab2.UCount * 100)) > 10

    please, help me

  14. Pinal:

    How does SQL Server 2005 issues a search inside a page that belongs to a clustered index? Does it scan the page or simply performs a binary search (I mean, since the records are ordered in a clustered index)?

    Best regards!

  15. Hi Pinal,

    I am currently working on migrating queries from DB2 to SQL Server 2005.

    Please refer to the following DB2 CREATE INDEX Query:

    create index XFK_ACTV_CTC on ACTIVITY (
    CNTX_TYP_CD ASC
    )
    allow reverse scans;

    In IBM-DB2 the syntax “ALLOW REVERSE SCANS” – Specifies that an index can support both forward and reverse scans; that is, scanning of the index in the order that was defined at index creation time, and scanning in the opposite order.

    I have been trying to search for an equivalent syntax in SQL Server 2005, however I am not able to do so.

    Kindly help me in searching it.

    Thanking you in anticipation.

    Best Regards,
    Amit

  16. Remember the data size folks. If an index has 40 bytes but the table has 400 bytes per row, the IO increases by a factor of 100 if all the data is together (read reorg). Index will always be faster from a pure IO perspective because the IO buffers fetch more ‘qualifying’ data per physical IO read than a table scan.

  17. Hi Pinal Dave,

    I am trying to implement index by using substring function but its throwing an error so could u suggest me for better solution.

    Generally, implementing this substring function in index for getting the index seek on the query.

    Below is the syntax.

    create nonclustered index IX_CreatedBy on TransDetails
    (substring(createdby, charindex(‘\’,CreatedBy) + 1, len(CreatedBy)))

    in the db having the value like \
    at this moment cannot change the table structure for this query alone.

    Regards,
    Mohanraj Jayaraman

  18. Pinal,

    Your explainations are really amazing.

    I have seen couple of times with SQL Server 2000 that for some reason, query optimizer do not use index on its own and query finishes in about 8 min while if I give hint(explicit) then it returns output in less than a min. I checked index frag and update stats.. all are looking fine.

    Can you please advice ?

    Thanks,
    sunil

  19. Pinal,

    Here’s the query i was talking about.. I tested it in SQL 2000 and SQL 2005 both…

    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SET @StartDate = ‘2008-09-11 16:00:00′
    SET @EndDate = ‘2008-09-12 00:00:00′
    SELECT * FROM PCHDotCom.dbo.BillMeEntries WITH (NOLOCK, INDEX=IX_BME_CREATED)
    WHERE
    Created >= @StartDate AND
    Created < @EndDate
    ORDER BY
    1 DESC

    If I remove the index hint, the query plan will decide to do clustered index scan and the query takes drastically longer

    Please help ASAP.

    Thanks,
    Sunil

  20. Hi,

    Here is my Select statement

    UPDATE FCE
    SET
    –FCE.RECAT_CREDIT_CMT = RIID.CREDIT_CMT,
    FCE.RECAT_DEAL_ID = RIID.DEAL_ID,
    FCE.RECAT_STATUS = RIID.STATUS,
    FCE.RECAT_STATUS_DT = RIID.CLM_STATUS_DT,
    FCE.RECAT_EUV_FLAG_ITEM = RIID.EUV_LINE_FLAG,
    FCE.RECAT_EUV_FLAG = RIID.EUV_FLAG,
    FCE.M_CODE = RIID.MCODE,
    FCE.RECAT_EUV_SUBMIT_DT =RIID.EUV_SUBMIT_DT,
    FCE.RECAT_EUV_CONTROL_DT =RIID.EUV_CONTROL_DT,
    FCE.STAKO_DEAL=RIID.STAKO_DEAL,
    FCE.MMP_FLAG=RIID.MMP_FLAG
    FROM FinClaimEntries FCE, RECAT_ITEMS RIID
    where FCE.CLAIM_ID = RIID.ECLAIM_ID
    AND (FCE.recat_prod_item_id = RIID.ECLM_ITEM_ID
    OR FCE.recat_bdl_item_id = RIID.ECLM_ITEM_ID
    OR FCE.CLAIM_ITEM_ID = RIID.ECLM_ITEM_ID
    )
    AND RIID.CLM_ID = (SELECT MAX(CLM_ID) FROM RECAT_ITEMS RIID2 where FCE.CLAIM_ID = RIID2.ECLAIM_ID )

    Here is Number of Records Details

    FinClaimEntries – 78,589,25
    RECAT_ITEMS – 97,256,86

    Please let me know yours suggestion for Index Creation & Joins

    Thanks,
    Sunil

  21. Hi to all,

    I should optimize a very simple query (select) where the actual execution plan presents a clustered index cost of 56 % due to estimated cost operator. how can i reduce this cost??

    thanks

  22. Hi Pinal,
    Could you explain the Previous syntax:
    SELECT * FROM PCHDotCom.dbo.BillMeEntries WITH (NOLOCK, INDEX=IX_BME_CREATED)
    WHERE
    Created >= @StartDate AND
    Created < @EndDate
    ORDER BY
    1 DESC
    =====
    Is the Inde = index name a Valid syntax if so how does it help performance wise??

  23. Hi Pinal,

    I have a table with abt 200K records. a “where clause” on a non-indexed field causes a “clustered index scan”. It works fine and fast, but perf degrades when concurrent users increase.

    do you think a clustered index scan can be a good reason for perf hit with increase in concurrent users even in a table of this size. OR maybe i should keep looking further for other causes.

    rgds

    Ajay

  24. @Ajay,

    If you have sql server 2000 then
    1. Why dont you run Index Tuning Wizard, analyze your query, result of which recommends any improvements can be done on indexes for that query.

    If you have SQL Server 2005,
    1. Use Database Engine Tuning Advisor. This does the same thing, it will give you recommendations about indexes for that particular query.

    Regards,
    IM.

  25. Hi Pinal,
    I wanted to ask if there is something similar to the horizontal partitioning mechanism(automatic physical distribution of partitioned table in ms SQL 2005 SE).
    Would be very useful if you could suggest something similar to this mechanism(if the same is not available in MS SQL 2005 SE)

  26. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  27. Is there a way to make mssql aware that it needs to do an index seek rather than a table scan? The proper indexes exist, and the statistics have been updated to my knowledge. On one system, a join between two tables results in a table scan in one table. One a separate database with the same schema/indexes, it does an index seek on both tables in the join. How do I make mssql aware it needs to do an index seek on the one system?

  28. i have a query the where clause which is convered in non-clustered index and partial non-key column which is convered in the non-clustered index as well. But the execution plan show me the clustered index scan = 98 %, can i have any solution to tunning this scenerio ?

  29. There is some difference between Index scan and Table scan, that is, when we have index scan sql engine locks only PK not whole ROW, and because of that is faster than table scan

  30. This is a wonderful topic and I’m glad I found it as it’s the exact answer to a quest that I had about a table today. It helped reduce my query time from 14 seconds to less than 1!

    Great work!
    Tim

  31. Hi,

    I have a select statement. How do I know if it is using the index which i created on one of the columns (and this column is included in the select list)

  32. 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.

    • 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.

  33. 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.

    • 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.

  34. 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?

  35. 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.

  36. 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.

  37. 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?

  38. 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]

    • 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

  39. 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?

  40. 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.

  41. 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

  42. 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

  43. 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.

  44. Hey Pinal plz can you tell me the answer of the given question

    There is 1 table ‘T’ as follows
    id ch na flag
    2 A jack Y
    5 b amy N
    1 $ adams N
    3 * anna Y
    7 @ rose N
    4 & smith Y
    6 ! sue Y

    Create nonclustered index nc_T on T(id,ch,na)

    Which of the below ‘select’ statements does NOT perform an index seek?
    • select na from T where ch = ‘!’
    • select na from T where id = 6 and ch = ‘!’
    • select na from T where ch = ‘!’ and id = 6
    • select na from T where na = ‘sue’ and id = 6 and ch = ‘!’
    • Leave blank

    • select na from t where ch=’!’ is not perfor an index seek
      here when ever we apply on where conditon that time that column jonly datatype is string type .so that query is not erform index seek

    • If your SUBSTRING function is inside WHERE clause, then answer is none. In that case, SQL Server has to retreive all records and then perform substring on them.
      To avoid this situation you should create persisted calculated field and then create nonclustered index on it

  45. Hi Pinal. Thanks for this great website where we can all share knowledge.

    Can someone please help me improve the performance of a simple query? There are two tables, MAIN and DETAIL, and each have a clustered PK called ID (bigint). DETAIL has an indexed column cMAIN that is the ID from MAIN (basically a foreign key without being explicitly set as such). When I query MAIN based on parameters such as date, results are instantaneous.

    When I use an INNER JOIN or sub-query to link MAIN and DETAIL, SQL Server uses a Clustered Index Scan (62%) on the PK of DETAIL and a Hash Match (38%) to return records from DETAIL. MAIN has 48 million rows (8 GB data, 3 GB index) and DETAIL has over 50 million rows (5 GB data, 3 GB index). The clustered index scan checks all 50 million rows even though the result set contains less than 1000 rows. The where clause is based on MAIN only, and the query is similar to SELECT [DETAIL].* FROM [MAIN] INNER JOIN [DETAIL] ON [MAIN].[ID] = [DETAIL].[cMAIN] WHERE [MAIN].[StartDate] BETWEEN… AND … All columns from DETAIL are needed in the result set.

    Is there anything that can be done to force or trick SQL Server to use an index seek instead of a scan? In my opinion, the query is very selective and SQL Server should use a seek on the available index. No suggestions are presented in the actual execution plan. The database is a replication with writes coming from the publisher only. I am using SS 2005 64-bit on Windows Server 2003 R2 64-bit. My hardware is unlikely to change (maximum ram already @ 32 GB, SSD on PCIe, two quad core XEON 5335 from 2007). Any help would be greatly appreciated.

  46. Try using covered index etc, or if you are using this query in a function and not stored procedure then this will happen, if you still require a function and cannot convert it into sp then use a view for that query

  47. In case of non clustered index, index key columns and data in table are stored on different pages. Firstly index key columns are scanned and the column key value which meets the criteria in the predicate condition is then referred to obtain data from a table based on row . So full scanning of table does not take place…Please correct me if I am wrong and If this is the case what is the difference in seek and scan.

  48. May I understand that Index Scan and Table Scan are the same thing? If an Index Scan is performed, it means that the index/s are not being used for that particularly query?. Is this correct?

  49. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

  50. Hi Pinal,

    Can i partitioned a table which is already used in Merge replication or Peer to Peer replication as an article.
    i.e. Table XYZ is replicated using merge or Peer to Peer replication from server S1 to Server S2. Table is of 700Gb and i want to partitioned the tables.

    Please, let me know how to do it. What is the effect of that on publisher and subscriber in merge replication ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s