The non-equality operators, < > and !=, are special cases. The query optimizer checks whether it should cover nonclustered indexes if the column is indexed, and uses a nonmatching index scan if an index covers the query. However, if the index does not cover the query, the table is accessed through a row ID lookup of the data pages during the index scan.
Shown below are examples of clauses that can be fully optimized. If there are statistics on these columns, they can be used to help estimate the number of rows the query will return. If there are indexes on the columns, the indexes can be used to access the data.
au_lname = “Bennett" price >= $12.00 advance > $10000 and advance < $20000 au_lname like "Ben%" and price > $12.00
These search arguments cannot be optimized unless a functional index is built on them:
advance * 2 = 5000 /*expression on column side not permitted */ substring(au_lname,1,3) = "Ben" /* function on column name */
These two clauses can be optimized if written in this form:
advance = 5000/2 au_lname like "Ben%"
Consider this query, with the only index on au_lname:
select au_lname, au_fname, phone from authors where au_lname = “Gerland” and city = "San Francisco"
The clause qualifies as a search argument:
au_lname = “Gerland"
There is an index on au_lname
There are no functions or other operations on the column name.
The operator is a valid search argument operator.
This clause matches all the criteria above except the first; there is no index on the city column. In this case, the index on au_lname is used for the query. All data pages with a matching last name are brought into cache, and each matching row is examined to see if the city matches the search criteria.
