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 (https://blog.sqlauthority.com), Predicates – BOL
39 Comments. Leave new
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!
It is really a usefull information for DBA while reading exec. plan
I agree with Matija!
Thanks!
I agree with Matija! Thanks!
Rakesh – Thanks for the comment.
Yes, please more articles of this nature. I appreciate your website for the concise, clear, and not overwordy articles. You are awesome!
Robyn
I liked this article very much. I just want a clear idea about what you mean by “row matching” in index seek.
DEAR SIR, I AM SANTOSH KUMAR SAMANT. I WANT TO SOME IMPORTANT SQL2005 QUESTIONS. SO PLEASE SIR, GIVE ME SOME IMPORTANT QUESTIONS
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
Hi Ashish
According my knowlegde Sequence is index seek,index scan,table seek and table scan.
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
I like this kind of articles, this is really good n i can understand the depth of the sql server how it works
Thanks
Dins!
Hi pinal
Thanks for a very good article in index
rajeev
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.
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
Such articles are indeed interesting …. Would definately like to see more…. Thanks Pinal.
Interesting article. I prefer to say (when related to predicates), that “Indexseek” is used by engine when a very low percentage of rows of table satisfy the predicate.
As you guessed I’s looking for this kind of articles.
Thanks.
I want more example for index scan and index seek
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
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.
I too am interested in more articles like this, especially about indexes.
Agree with all, this give us an insight of functionoing of SQL server.
More such work appreciated.
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!!
how to separate date and time