Microsoft SQL Server 2008 R2 Unleashed- P128:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1214 CHAPTER 35 Understanding Query Optimization Identifying Search Arguments A SARG is defined as a WHERE clause that compares a column to a constant. The format of a SARG is as follows Column operator constant_expression and. SARGs provide a way for the Query Optimizer to limit the rows searched to satisfy a query. The general goal is to match a SARG with an index to avoid a table scan. Valid operators for a SARG are and BETWEEN and LIKE. Multiple SARGs can be combined with the AND clause. A single index might match some or all of the SARGs ANDed together. Following are examples of SARGs flag 7 salary 100000 city Saratoga and state NY price between 10 and 20 the same as price 10 and price 20 100 between lo_val and hi_val the same as lo_val 100 and hi_val 100 au_lname like Sm the same as au_lname Sm and au_lname Sn In some cases the column in a SARG might be compared with a constant expression rather than a single constant value. The constant expression can be an arithmetic operation a built-in function a string concatenation a local variable or a subquery result. As long as the left side of the SARG contains a column it s considered an optimizable SARG. Identifying OR Clauses The next statements the Query Optimizer looks for in the query are OR clauses. OR clauses are SARGable expressions combined with an OR condition rather than an AND condition and are treated differently than standard SARGs. The format of an OR clause is SARG or SARG or . with all columns involved in the OR belonging to the same table. This IN statement column in constantl constant . is also treated as an OR clause becoming this column constantl or column constant or . Some examples of OR clauses are as follows where au_lname Smith or au_fname Fred where type business and price 25 or pub_id 1234 where au_lname in Smith Jones N A Download from Query Analysis 1215 An OR clause is a disjunction all rows matching either of the two criteria appear in the result set. Any row .
