Efficiency of Filters
The efficient use of queries depends in part on the way you construct filter expressions, and in part on the way the administrator constructs table indexes. Programmers and administrators can use these rules of thumb to help promote efficiency and high performance.
Keys and Indexes
Rule of Thumb: Construct filter expressions in which every predicate refers to a key or index.
A filter expression that does not refer to any column in a key or index, results in a full table scan that is inefficient.
Left-Most Columns
Rule of Thumb: Construct filter expressions that reference the left-most columns of an index using the predicates =, ==, <=, >=, <, >, IN, or BETWEEN.
When an index includes more than one column, the administrator has defined them in a specific order: from left to right. Queries with filter expressions that refer to the left-most column of an index can be more efficient than filter expressions that skip the left-most column and instead refer only to columns to its right. Similarly, queries with filter expressions that refer to the left-most two columns can be even more efficient. Queries can achieve maximum efficiency when they use filter expressions that refer to all the columns of an index.
In contrast, omitting the left-most column from the filter expression results in a full table scan, which is the least efficient.
The order in which the columns appear within the filter expression does not affect efficiency. Only the order of columns when defining the index matters.
Avoid Left-Most NOT
Rule of Thumb: Do not construct filter expressions that reference the left-most columns of an index using the predicates NOT, IS NOT, !=, <>, IS, ISNULL, IS NULL, NOTNULL, NOT NULL, and IS NOT NULL.
In contrast to the rule of left-most columns, filter expressions that reference the left-most columns with these operators have the opposite effect: to guarantee a full table scan, which is the least efficient.
The presence of the predicate IS in this list could be counterintuitive. See the next rule.
lastname='Smith' and firstname IS NOT 'Dan'
Avoid IS
Rule of Thumb: Use the predicates = or ==, rather than IS.
Even though the predicates =, ==, and IS are semantic synonyms, the behavior of IS differs dramatically. Namely, IS guarantees a full table scan, which is the least efficient.
Bound Ranges from Both Ends
Rule of Thumb: When using the predicates > or >=, which specify a lower limit on a column's value, also include the opposite predicates, < or <=, to specify an upper limit on the same column.
A query searches an index from its lower limit to its upper limit. If you omit the upper limit, the query continues searching to the end of the index. If you omit the lower limit, the query begins with the first row of the index.