WayneS (12/15/2010)
Craig Farrell (12/15/2010)
Actually, the WHERE 1=1 is a neat little trick to avoid doing extra if statements to determine if something starts the clause and needs an AND. Since 1=1 is always true, it never evaluates (as far as a limited testing I did showed), but saves you some complex semantics, since all statements, no matter if first or not, can start with the AND field = @_param1 structure.I think that this should be qualified with: "when writing dynamic SQL". It is incredibly useful then; and utterly useless any other time.
The optimizer can strip off this kind of expressions using a feature called "contradiction detection", that prevents the engine from evaluating expressions that are always true or false (tautologies or contradictions).
Tautological expressions don't affect performance, on the contrary, they can boost them. Try issuing that query changing "1 = 1" to "1 = 2" and look at the exec plan: it will be a single constant scan.
-- Gianluca Sartori