Examples of SARGs

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" 
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.