In this article we shall examine the two modes of data search and retrieval using indexes- index seek and index scan, and the differences between the two.
Firstly, let us revisit indexes briefly. An index in a SQL Server database is analogous to the index at the start of a book. That is, its function is to allow you to quickly find the data you are searching for inside the book; in the case of a database, the “book” is a table.
An index scan means that SQL Server reads all rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all of the rows of the index are examined instead of the table directly. This is sometimes contrasted to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.
You may wonder why the Query Optimizer may choose to do an index or table scan. Surely it is much faster to first look up data using an index than to go through all the rows in a table? In fact, for small tables data retrieval via an index or table scan is faster than using the index itself for selection. This is because the added overhead of first reading the index, then reading the pages containing the rows returned by the index, does not offer any performance improvement for a table with only a few rows.
Other reasons to use an index scan would be when an index is not selective enough, and when a query will return a large percentage (greater than 50%) of rows from the table. In such cases the additional overhead of first using the index may result in a small degradation of performance.
An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage (less than 10 or 15%) of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; this is highly beneficial, in performance terms, when a table has a very large number of rows.
It is also worth noting that it is usually not worthwhile to create indexes on low-cardinality columns as they would rarely be used by the Query Optimizer. A low-cardinality column is one that contains a very small range of distinct values, for example a ‘Gender’ column would have only two distinct values- Male or Female. An example of a high-cardinality column is of course the primary key column, in which each value is distinct.
In summary, the Query Optimizer generally tries to perform an index seek. If this is not possible or beneficial (for example when the total number of rows is very small) then an index scan is used instead.
Reference : Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
Really nice explanation of
Indexing and very useful for developers.
As always a pleasure to read.
Not to much information to over load first thing on a Monday but just enough to get you started!
Question – do you mean:
An index scan means that SQL Server reads all rows in a table, and then returns only those rows that satisfy the search criteria.
or
An index scan means that SQL Server reads all rows in a INDEX, and then returns only those rows that satisfy the search criteria.
?
I think it was meant to say ‘A table scan means ….’. The definition of an index scan then follows in the next sentence.
How we can find the selectivity of column to create index?
Can you give me description?
How much rows (minimum require for selectivity to create index?
Suppose one table have 200 rows, in this 175 rows are same ans 25 are distinct ? then will it be fine to create an index on that column?
or Can you give me exact scenario when column is liable for selective for index?
Hi Pinal,
This post is a dangerous mix of poorly worded sentences and incorrect facts.
The fact that you are with Solid Quality Learning and an MVP is astonishing and is a testament to the quality of those organizations.
I feel sorry for the people that look to you for information and guidance.
Best of luck, Jason
Hi Paresh,
Some tips on Selectivity:
Example with good Selectivity
A table having 100’000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88’000 / 10’0000 = 0.88.
The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index’s selectivity is good if few rows have the same value.
Example with bad Selectivity
lf an index on a table of 100’000 records had only 500 distinct values, then the index’s selectivity is 500 / 100’000 = 0.005 and in this case a query which uses the limitation of such an index will retum 100’000 / 500 = 200 records for each distinct value. It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.
select count (distinct job) “Distinct Values” from emp;
Distinct Values
—————
5
select count(*) “Total Number Rows” from emp;
Total Number Rows
—————–
14
Selectivity = Distinct Values / Total Number Rows
= 5 / 14
= 0.35
Mark,
Hope this article will clear it up. Pinal hope you don’t mind me answering the question:
An index scan is a complete scan of all the pages in a non-clustered index.
A clustered index scan is a complete scan of all pages in a clustered index (ie, the table itself)
Neither scan uses the b-tree structure of the index, but just reads the leaf pages in order, using each page’s reference to the next in the chain.
An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.
A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf.
This one explains difference….seek means using the B-tree structure and hence performance improvement…..thanks
Hi,
Often an index needs to be very selective before it will be used, not 50% as you state but often 10% or lower.
Thanks
Ian
Great description. It’s exactly what I was looking for.
It clarified me many things about indexes and execution plans explanation.
Thanks.
Dave you are greatest!
Good one!
Hi Ramdas,
So Selectivity ratio is more or around 80-90 percentage is the best option to create for index, Right?
Thanks all for you help.
And one thing also ,
select name , city from tables where id = 2
select * from tables where id = 2
Two indexes are created on table as following :
create index IC_Col1 on table(id) include (name, city)
create index IC_Col2 on table(id)
1. which of above index is used for
select name , city from tables where id = 2
2. create index IC_Col2 on table(id) can be used for both query
select name , city from tables where id = 2
select * from tables where id = 2
I have created script to find selectivity of each columns for every tables. In those some tables with less than 100 rows but selectivity of column is more than 50%.
So, is those column are eligible for index?
Or, can you tell, how much minimum rows require for eligibility for creating index?
Thanks
Pinal, thanks for the article. One of the problems I had was following the logic. I’m not sure it simplifies any more than BOL and other white papers on MS website that show scans and seeks at work. Furthermore you may have use used “table” and “index” interchangeably in one or more places. In any case, MS tweaks the cost algorithms for each server version. That’s why we have to use the profiler and plan guides as tools to make the final choices for a specific application.
Hi Pinal & All,
Really very good explanation of index scan & index seek. And by your article every one will familier with how can make faster seeking objects by index. And also you have cleared in your article for seeking the objects when it will fetching by leaf level and when it will fetching by table.
Also I have a question? Thats the general question off all interviewer..
If Yesterday on the Server T-SQL statement was running fine but today it’s take too much time. What are the reason?
Thanks & Regards,
Rajiv Singh
Hi Pinal,
Very gud article.
Could you please tell me what is the cons of using index ?
Thanks.
Han.
Hi Pinal,
Please write an article about the Selectivity, describing the situations where we shall create index in a column.
Also it will be great to have an article about the Fill Factor and its practical usage.
Thanks again for the valuable articles.
Thanks a lot for such a nice article.
It is concise and clear. Keep us updated in this way….
Please write an article about the jobs created under Transaction Replication with their functions.