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
hai
to improve the performance i partition my table based on a column.but when updating it gives an error
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??
Hi,
Can you tell that which data structures actually columns uses to store the data which has some indexes applied?
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
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.
nice a lot!!!
thanks very nice sir
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” –
If possible can u explain the diff between index seek and scan through diagram/picture ?
Hi Pinal,
Colud you tell me for 6+ sql developer what are all the topics will ask in interviews.
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.
The article would be a little more understandable if we could see examples somehow.
Good one…
As per article prefer to give multiple index on single column instead of single index on multiple columns..?