I've been working on optimizing a query and have ran into a situation that's making me question how I've always used SQL's OR operator. (SQL Server 2000 also)
I have a query where the conditional (WHERE) clause looks something like this:
WHERE (Column1 = @Param1 or Column1 LIKE @Param1 + '%')
AND (@Param2 = '' OR Column2 = @Param2 OR Column2 LIKE @Param2 + '%')
Now, I've always understood that OR in SQL evaluated both expressions. So all records that evaluated true for the left expression would be returned along with all records that evaluated true on the right expression. For example:
SELECT * FROM TABLE1
WHERE COL1 = 'Test' OR Col2 = 'Data'
This would return back all records where COL1 is'Test' as well as any record where Col2 is 'Data'
In the example above, I modified the Column2 conditional to the following:
AND(Column2 LIKE ISNULL(@Param2, '') + '%')
All of the sudden, I get 0 rows returned.
Have I been mistaken in that OR only evaluates expressions until it find a TRUE result or is there a condition that would cause the 2 different to return different results?
"OR only evaluates expressions until it find a TRUE result"
It only has to, but that's not your problem (actually this is what was saving you in your original case). Your two queries are not really equivalent.
I'm thinking you have NULLs in Column2 which will never cause (Column2 LIKE ISNULL(@Param2, '') + '%') to be true - and in your original version the @Param2 = '' was masking this case, since it IS true (sometimes)
Perhaps:
(ISNULL(Column2, '') LIKE ISNULL(@Param2, '') + '%')
Remember the three-valued logic for NULLs:
TRUE and UNKNOWN: UNKNOWN
TRUE or UNKNOWN: TRUE
FALSE and UNKNOWN: FALSE
FALSE or UNKNOWN: UNKNOWN
But I'm not sure your optimization is really helping.