The difference between index seek and index scan

Index seek is a SQL Server execution query statement using the established index to find, the index is a B-tree structure, SQL Server first find the root node of the index tree, one level down to find the corresponding leaf node, after the leaf node to remove the data. For a clustered index, the leaf node is the data for the entire table, able to get data for all columns, and for a nonclustered index, the leaf node stores the data of the indexed column, and if the index has columns, the leaf node stores the data that contains the columns, the data that gets is the indexed column and the containing column, and if the data for other columns Key lookup is required to retrieve the data from the source table based on the "line address" information contained in the Index leaf node, which is the category of index seek, but the key Looup (bookmark lookup). Index seek is ideal for queries that return a small number of records from a table with large amounts of data.

The index scan is a direct traversal of all leaf nodes of the index tree, similar to the table Scan.

Reference articles

Http://www.cnblogs.com/wuxiaoqian726/articles/2015519.html

SQL has a query Optimization parser query Optimizer, which is analyzed first before executing the query, and when there are indexes available in the query, it will prioritize the efficiency of querying with index seek, and when using the index seek query is inefficient and not good. It will be queried using the index scan. Under what circumstances would that result in index seek efficiency lower than index scan?

1. When there is not much data in the table to be queried, the index seek efficiency is not necessarily high, so use index seek to start with the index tree and then use the leaf node to find the corresponding row. In the case of fewer rows in the tree, the index scan has not been done directly.

2. When the amount of data returned is large, the index seek efficiency is not necessarily good when the amount of data returned is 50% or more than 50% of the total amount, and the best performance is achieved by using index seek when the amount of data returned is 10% to 15%.

3. In cases where the indexed columns are in a consistent number of values, indexing is not necessarily a good efficiency to build. In fact, the reason is very simple, when the index of the column to change the value of the case, the establishment of the index binary tree is chunky type, the tree level is not high, many rows of information are contained in the leaves, such a query is obviously not good use of the index.

Reference articles

http://blog.sqlauthority.com/2009/08/24/sql-server-index-seek-vs-index-scan-diffefence-and-usage-a-simple-note/

SQL Server–index Seek vs. Index Scan–diffefence and Usage–a simple Note

In this article we shall examine the both modes of data search and retrieval using Indexes-index seeks and index scans, an d The differences between the.

Firstly, let us revisit indexes briefly. An index in a SQL Server database was analogous to the index at the start of a book. That is, it function is-to-quickly find the data searching for inside; In the case of a database, the ' book ' is a table.

An index scan means this SQL Server reads all rows in a table, and then returns only those rows that satisfy the search CR Iteria. When an index scan was performed, all the rows in the leaf level of the index was scanned. This essentially means, all of the rows of the index is 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.

Wonder why is the Query Optimizer may choose to does an index or table scan. Surely it is much faster to first look up data using a 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 was because the added overhead of first reading the index, then reading the pages containing the rows returned by the Index, does not an offer of any performance improvement for a table with only a few rows.

Other reasons to use an index scan would was when an index is not selective enough, and when a query would return a large PE Rcentage (greater than 50%) of the 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. The Query Optimizer relies entirely on the Dex Leaf data to locate rows satisfying the query condition. An index seek would be is most beneficial in cases where a small percentage (less than or 15%) of rows would be returned. An index seek would only affect the rows of satisfy a query condition and the pages that contain these qualifying rows; This was highly beneficial, in performance terms, when a table had a very large number of rows.

It's also worth noting that it's usually not worthwhile to create indexes on low-cardinality columns as they would rarel Y is used by the Query Optimizer. A low-cardinality column is one it contains a very small range of distinct values, for example a ' Gender ' column would h Ave only distinct Values-male or Female. An example of a high-cardinality column was 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 are very small) then a index scan is used instead.

The difference between index seek and index scan