SQL SERVER – Difference Between Index Scan and Index Seek

I have explained the concept of Index Scan and Index Seek earlier but I keep on receiving the same question again and again. Let us today look into it with little more depth.

Before we go over the concept of scan and seek we need to understand what SQL Server does before applying any kind of index on query. When any query is ran SQL Server has to determine that if any particular index can be applied on that particular query or not.

SQL Server uses search predicates to make decision right before applying indexes to any given query. Let us see the definition of predicates from Book On-Line: Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

In you have not understood so far what I am trying to suggest. Let me put this in simple words in following scenario.

Table1 has five columns : Col1,Col2,Col3,Col4,Col5
Index1 on Table1 contains two columns : Col1,Col3
Query1 on Table1 retrieves two columns : Col1,Col5

Now when Query1 is ran on Table1 it will use search predicates Col1,Col5 to figure out if it will use Index1 or not. As Col1,Col5 of Query1are not same as Col1,Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.

Index Scan happens when index definition can not point close on single row to satisfy search predicates. In this case SQL Server has to scan multiple pages to find range of rows which satisfy search predicates. In case of Index Seek SQL Server finds single row matchign search predicates using index definition.

I can write more on this subject but I want to make sure that my readers like this kind of articles. Let me know your thoughts and ideas about this subject.

If you want to read more article similar to this, please use SQLAuthority customized search engine at Search@SQLAuthority.com.

Reference : Pinal Dave (http://blog.SQLAuthority.com), Predicates – BOL

37 thoughts on “SQL SERVER – Difference Between Index Scan and Index Seek

  1. Personally, I like this kind of articles …
    It gives me kinda in-depth understanding of how SQL Server works, therefore it gives me greater chance to use it the right way …
    The article is short enough but still full of useful information.
    Great! Thanks!

    Like

  2. Hi Pinal,

    Thanks for a very good article on index.

    I want to know what is difference between INDESX SCAN, INDESX SEEK, TABLE SCAN and TABLE SEEK…And what will be the preference order in term of optimization.

    Ashish

    Liked by 1 person

  3. Understanding how the sql server basically work or reacts to the query is very important. Help us to understand the internals by writing more on this

    Thanks
    Ganesh

    Like

  4. hi pinal,
    I have a question, Can i create the dynamic indexes on the predicates that i am using in the query of a sp and drop it right after executing the query. Is it the right way to create and drop indexes so that the optimizer uses the correct index and Index seek will not arise.

    Like

  5. I really like this kind of articles.

    Just one question. I’ve heard that any kind of scan is bad, no matter if it’s a table or index scan. Sometimes in my queries I see an Index Scan, but I can not turn it into an Index Seek even if by changing or reordering index columns. Please explain the reason and tell us the ways to get rid of Index Scans.

    Thanks

    Like

  6. Pinal

    Thanks for short , simple yet effective information on index scans and seeks. Of course, I (and I am sure all others readers also) will truly appreciate if you can expand more on this subject

    Like

  7. This article is really useful , and example is very nice to understand. I am looking for this type of example.
    Can u provide this type of article regrading INDEX rebuild and reorganize.Thanks a lot.

    Like

    • Whether we like this kind of article or not is irrelevant. Anyone who wants to write efficient queries better understand it; which is why I’m trying to! This is the least enjoyable part of writing queries, but the most rewarding and necessary.

      I Appreciate this resource, thank you!!

      Like

  8. Hi Pinal,
    I have a simple question, Does the columns used in SELECT clause affect the index usage? e.g. in the scenario (given in article) if we write follwing queries:
    SELECT col4, col5 FROM table1 where col1 = ‘abc’ and col3 = ‘xyz’

    SELECT col1, col3 FROM table1 where col1 = ‘abc’ and col3 = ‘xyz’

    SELECT col1, col4 FROM table1 where col1 = ‘abc’ and col3 = ‘xyz’

    As indexes are on col1 and col3, how these queries will behave in terms of index utilization??

    Like

  9. Hi,
    Your articles differs in difficulty levels. As You have readers from all levels of intelligence.
    So please carry on these types of articles b/c these are very helpful for normal readers.
    Thanks
    Azhar

    Like

  10. Hi,
    I have six table in my query and while looking at the execution plan i found that one of my table is showing Index scan without any kind of predicates. Please let me know whether this query is good or should i optimise this query.

    Like

  11. Please take this as constructive criticism.
    This article needs to be proof-read with a fine-tooth comb for grammatically correct English.
    You are too verbose when it comes to insignificant details and those that need elaborate explanation, you seem to just gloss over them. Case in point — “Index Scan happens when index definition can not point close on single row to satisfy search predicates” -

    Like

  12. Partial Error, in my opinion.

    If ‘ [Index1 on Table1 contains two columns : Col1,Col3 ] ‘ – either Table-Field Value (column) has a null value and filtering is not used; then a possible Index Seek is in fact an Index Scan on 2008 and later SQL Servers versions, in my opinion, by Engine Design standards.

    Like

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

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