Differences between scan and seek in SQL Server

★ ★ ★★ ★ ★★ ★ ★★★ ★APGC DSD Team                                                   

APGC DSD Team

Microsoft

12,587 points                 5                 2                 1                         Recent achievementsForums answerer IFirst Answer confirmed first Forums Reply View profileAugust 1,1

• Share

• 0
• 0

SQL Server uses scanning (scan) and lookup (seek) algorithms to read data from both the data tables and indexes. These two algorithms form the basis of the query and are almost ubiquitous. Scan scans and returns the entire table or the entire index. Seek is more efficient, according to the predicate (predicate), returning only the data within one or more ranges within the index. The following query statements are used as examples to analyze scan and seek:

select OrderDate from Orders where OrderKey = 2

Scan

With scan, SQL Server reads each row of data in the Orders table and evaluates whether the predicate "where order=2" is satisfied when read. Returns the row if it is satisfied (the data row meets the criteria). In this example, we call this predicate "residual predicate". For optimal performance, SQL uses "residual predicate" as much as possible in the scan. However, if the overhead of residual predicate is too expensive, SQL Server may use a separate "filter iterator". "Residual predicate" appears in the text-formatted plan in the form of a where keyword. Plan for XML format is the form of the <predicate> tag.

The following is the result of the plan for the scanned text format:

|–table Scan (OBJECT: ([ORDERS]), Where: ([orderkey]= (2)))

Explains how to scan:

Scan reads every single data in the table regardless of whether the data row satisfies the criteria, so the cost of scanning is proportional to the amount of data in the table. Therefore, if the table is small or most of the data in the table satisfies the predicate, scan is an efficient way to read. However, if the table is large or the majority of the data does not satisfy the predicate, this approach allows us to access too many unnecessary data pages and perform additional IO operations.

Seek

Continue with the above query as an example, if there is an index on the Orderkey column, then seek may be a good choice. With seek access, SQL Server uses the index to direct to the data row that satisfies the predicate condition. In this example, we refer to this predicate as "seek predicate". In most cases, SQL Server does not have to re-evaluate "seek predicate" as "residual predicate". The index guarantees that "seek" returns only rows of data that match the criteria. "Seek predicate" appears in the text-formatted plan in the form of the Seek keyword. For plan in XML format, the <seekpredicates> tag appears.

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

|–index Seek (OBJECT: ([orders].[ OKEY_IDX]), SEEK: ([orderkey]= (2)) ORDERED FORWARD)

when you use Seek, SQL Server accesses only the data rows and data pages that satisfy the criteria, so its cost is only proportional to the number of rows of data that meet the criteria and its corresponding data pages, and the amount of data in the base table is completely non-relevant. Therefore, seek is highly efficient for a predicate condition that is highly selective (through which most of the data in the table can be filtered out) .

The following table lists the various combinations of seek and scan lookup and heap tables, clustered indexes, and nonclustered indexes:

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

Differences between scan and seek in SQL Server