The difference between an index scan and an index lookup



One, Index scan

Similar to table scans, the index is scanned from the beginning to the end.

Second, index lookup

According to the characters you query, navigate to the local location of the index, and then start looking, do not have to scan the entire index, in the efficiency of the peso scan much faster.

second, what happens in SQL Server is likely to cause its execution plan to turn from index lookup to index Scan (index Scan)

1, implicit conversion is very easy to cause the execution plan from index lookup (index seek) into an index scan (index Scan)

There are two ways to avoid an implicit conversion of SQL:

(1) To ensure that the comparison has the same data type.

(2), using a cast (explicit conversion) method.

2. Non-SARG predicates can easily cause execution plans to change from index lookup to index Scan (index Scan)

SARG (searchable Arguments) also called query parameters

The most typical case of a statement that does not satisfy the Sarg form is a statement that includes a non operator, such as not,!=, <>;,!<;,!>;not EXISTS, don't in, and not, and so on, as in predicates using functions, predicates, and so on.

3,SQL query return data page (pages) reached the critical point (tipping points) is very easy to lead to index Scan (index Scan) or table scan (table Scan)

References: Index scan and Index lookup http://www.studyofnet.com/news/1040.html