Index Scan and Index lookup

Both scan and find operations are iterators used by SQL Server to read data from tables or indexes, and these are the most basic operations supported by SQL Server. Can be found in almost every query plan, so it is important to understand their differences, that the scan is processed on the entire table, that the index is processed at the entire page level, and that the lookup returns rows of data from one or more ranges of a particular predicate.

Let's look at an example of a scan

select[orderid]from[orders]where[requieddate]= ' 2015-03-21 '

In the Orders table, there is no index to the RequiredDate column, so SQL Server must read each row of the Orders table for the RequiredDate predicate of a few rows, and if the predicate condition is met, the row data is returned.

To maximize performance, SQL Server uses a scan iterator to estimate the predicate as much as possible, however, if the predicate is too complex or expensive, SQL Server may use a different filter iterator to estimate. The following is the process of text planning in the WHERE keyword:

|--clustered Index Scan (OBJECT: ([orders].[ Pk_orders]),
WHERE: ([Orders]. [requireddate]= ' 1998-03-26 '))

Describes the flowchart for this operation:

Because each row of data in the table is scanned, regardless of whether it is satisfied or not, its query cost is balanced against the total number of records in the table, when the data in the table is very small or the rows that satisfy the predicate are relatively long, the scan operation is effective, and if the amount of data in the table is larger or the rows that satisfy the predicate are I/O operations to get the data, this is not the most efficient method.

Let's look at an example of index lookups, the following example creates an index on the orderddate column: SELECT [OrderId] from [Orders] WHERE [OrderDate] = ' 1998- 02-26 '

This time SQL Server was able to use index lookups to directly find those rows of records that satisfy the predicate, which is called the "find" predicate. In most cases,SQL Server does not explicitly estimate the lookup predicate, and the index ensures that the find operation returns only the rows of data that are satisfied, and the following is the text plan for the find verb:

|--index Seek (OBJECT: ([orders].[ OrderDate]),

SEEK: ([Orders]. [Orderdate]=convert_implicit (datetime,[@1],0)) ORDERED FORWARD

Note: SQL Server automatically replaces the parameters in the query text with the @1 parameter

As a result, lookup scans only the data pages that satisfy the predicate, whose query overhead is obviously less expensive than the total number of records in the table, so lookups are often the most effective strategy for query predicate operations with high selection. That is, it is more efficient to use a lookup predicate for estimating data in large tables.

SQL Server distinguishes a scan from a lookup, as if it were scanned on a heap (an object without a clustered index), a scan on a clustered index, and a scan on a nonclustered index is partitioned. The following table describes the scan and find operations in these current query plans.

	Scanning	Find
Heap	Table Scan
Clustered index	Gather index to find a description	Clustered index Lookup
Nonclustered indexes	Index Scan	Index Lookup

predicate and overwrite columns that can be found

Before SQL Server performs an index lookup, it needs to determine whether the key of the index satisfies the predicate in the query, and we call the predicate "a lookup predicate," andSQL Server must determine whether the index contains or "overwrites" the column collection referenced in the query. The following describes how to determine which predicate is to be found, which predicate is not to be found, and which columns require an index overlay.

Single-column index

It is easy to judge whether a predicate is available on a single-column index, andSQL Server uses a single-column index to respond to most simple comparisons (including equality and inequality (greater than, less than, and so on)) or more complex expressions, such as functions that operate on columns and Like% predicates, these operators will prevent SQL Server from using index lookups.

For example, suppose we create a single-column index on the Col1 column, and we can index lookups on the following predicates:

• [Col1] = 3.14
• [Col1] > 100
• [Col1] Between 0 and 99
• [Col1] Like ' abc% '
• [Col1] In (2, 3, 5, 7)

You cannot use index lookups on the following predicates:

• ABS ([Col1]) = 1
• [Col1] + 1 = 9
• [Col1] Like '%abc '
• Here are some examples of single-column indexes:

First create some schema objects: Create table person (id int, last_name varchar (), first_name varchar ())

Create unique clustered index person_id on person (ID) create INDEX person_name on person (last_name, first_name)

Here are three queries and their respective text query plans, the first query is looked up on the person_name Index, the second query is first indexed on the first key column, and then the residual predicate is used to estimate the First_ Name, the third query cannot use an index lookup, but instead uses an index scan to process the residual predicate.

Select ID from the person where last_name = ' Doe ' and first_name = ' John '

|--index Seek (OBJECT: ([person].[ Person_name]), SEEK: ([person]. [last_name]= ' Doe ' and [person]. [first_name]= ' John ')]

Select ID from the person where last_name > ' Doe ' and first_name = ' John '

|--index Seek (OBJECT: ([person].[ Person_name]), SEEK: ([person]. [last_name] > ' Doe '), Where: ([person]. [first_name]= ' John ')]

Select ID from the person where last_name like '%oe ' and first_name = ' John '

|--index Scan (OBJECT: ([person].[ Person_name]), Where: ([person]. [first_name]= ' John ' and [person]. [last_name] like '%oe '))

Graph query plan for the above three queries:

Index Scan and Index lookup