SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note

In this article we shall examine the two modes of data search and retrieval using indexes- index seeks and index scans, 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 (http://blog.sqlauthority.com)

30 thoughts on “SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note

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

    ?

    Like

    • I think it was meant to say ‘A table scan means ….’. The definition of an index scan then follows in the next sentence.

      Like

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

    Like

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

    Like

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

    Like

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

    Like

  6. Great description. It’s exactly what I was looking for.
    It clarified me many things about indexes and execution plans explanation.

    Thanks.

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  13. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  14. Very nice query.

    With an extra field sic.key_ordinal and order by clause more readible, because the column fields are then in the exact order of the index:

    SELECT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sic.key_ordinal, sc.Name AS ColumnName,
    sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates, sis.last_user_seek
    FROM sys.dm_db_index_usage_stats sis
    INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
    INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
    INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
    WHERE sis.Database_ID = DB_ID(‘prdomgcod’) AND sis.OBJECT_ID in
    ( OBJECT_ID(‘oas_docline’), OBJECT_ID(‘oas_balance’))
    order by
    OBJECT_NAME(sis.OBJECT_ID), si.name, sic.key_ordinal, sc.Name,
    sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates, sis.last_user_seek
    GO

    Like

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

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