The following are some 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
The following search arguments cannot be optimized:
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 SARG:
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 SARG operator.
The datatype of the constant matches the datatype of the column.
city = "San Francisco"
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.
