Difference between SCAN and SEEK in SQL Server

From: http://blogs.msdn.com/ B /apgcdsd/archive/2012/08/01/sql-server-scan-seek.aspx

SQL SERVER uses scan and search algorithms to read data from data tables and indexes. These two algorithms constitute the foundation of queries, which are almost ubiquitous. Scan scans and returns the entire table or the entire index. Seek is more efficient. Based on the predicate, only data within one or more ranges of the index is returned. The following query statements are used as examples to analyze scan and seek:

select OrderDate from Orders where OrderKey = 2

Scan

Using Scan, SQL Server reads each row of data in the Orders table and evaluates whether the predicate "where order = 2" is satisfied during reading ". If the condition is met (the data row meets the condition), the row is returned. In this example, the predicate is called "residual predicate ". To achieve optimal performance, SQL tries its best to use "residual predicate" in scanning ". However, if residual predicate is too expensive, SQL Server may use a separate "filter iterator". "residual predicate" to appear in the plan in text format as the where keyword. The plan in XML format is in the form of a <predicate> flag.

The result of the plan in the text format of the scan is as follows:

| -- Table Scan (OBJECT :( [ORDERS]), WHERE :( [ORDERKEY] = (2 )))

The scan method is described as follows:

No matter whether the data row meets the conditions, the scan read method will access every data in the table. Therefore, the cost of scan is proportional to the total amount of data in the table. Therefore, if the table is small or most of the data in the table meets the predicate, scan is an efficient reading method. However, if the table is large or the vast majority of data does not satisfy the predicate, this method will allow us to access too many unnecessary data pages and execute more additional IO operations.

Seek

Continue with the preceding query as an example. If there is an index in the orderkey column, seek may be a good choice. With seek access, SQL Server uses indexes to direct data rows that meet the predicate conditions. In this example, the predicate is called "seek predicate ". In most cases, SQL Server does not have to reevaluate "seek predicate" to "residual predicate ". The index ensures that "seek" only returns qualified data rows. "Seek predicate" appears in the plan in text format in the form of the seek keyword. Plan in xml format is marked with <seekpredicates>.

The following is the result of the plan in the text format of seek:

| -- Index Seek (OBJECT :( [ORDERS]. [OKEY_IDX]), SEEK :( [ORDERKEY] = (2) ordered forward)

When using seek, SQL Server only accesses the data rows and data pages that meet the conditions. Therefore, the cost of SQL Server is only proportional to the number of data rows that meet the conditions and their corresponding data pages, the data volume of the base table is completely irrelevant. Therefore, seek is very efficient for a predicate condition with high selectivity (through which most data in the table can be filtered out.

The following table lists the search methods and heap tables seek and scan, and the combination of clustered index and non-clustered index:

	Scan	Seek
Heap	Table Scan
Clustered Index	Clustered Index Scan	Clustered Index Seek
Non-Clustered Index	Index Scan	Index Seek