Understanding predicate SARGability

A predicate is a filter that can be used to determine the set of conditions to apply to a query to trim the result set. As we have discussed in previous chapters, these are typically applicable to the following clauses:

• JOIN clauses, which filter the rows matching the type of join
• WHERE clauses, which filter source rows from a table or an index
• HAVING clauses, which filter the results

Most queries will make use of predicates, usually through a WHERE clause. When a predicate is serviceable by an index, it is said the predicate is SARGable, which is an acronym for Search ARGument-able. Having SARGable predicates should be a goal for our T-SQL queries because it can reduce the number of rows that need to be processed by a plan earlier in the execution – that is, when the data is being read by the SQL Database Engine. The implementation of this early row count reduction is called predicate pushdown; it is the action of using...