Gunnar Liknes (g_liknes.Tabortunderscores@g_lobal-satcom.com) writes:
Does MS SQL perform complete boolean evaluations? If (@P1=-1) it should
not have to check if (Field1 = @P1) because the result of the statement is
already determined.
The answer is that, yes, SQL Server is able to make logical shortcuts,
but that is not applicable here.
When SQL Server builds a query plan for a stored procedure, it builds
the plan for the entire procedure at once, and is thus blind to what
the actual values of variables and parameters at the time of the statement.
It does take in regard the values of parameter to build the plan, but
since it don't know whether parameter changes value in the procedure or
not, SQL Server can choose a plan which would yield the wrong result if
the parameter is changed. Moreover, since the plan is cached, the procedure
might be called with some other values the next time.
Thus if you have:
SELECT *
FROM tbl
WHERE (field1 = @p1 OR @p1 IS NULL)
AND (field2 = @p2 OR @p2 IS NULL)
It cannot look at @p1 and say "Hey @p1 is NULL, I don't have to test
Field1". So it must pick a plan where it accesses field1. No, once it
comes to the statement it could opt to not actually check field1, but
the cost is not the check - the cost is the access. In this case,
the optimizer will most like to scan the table from left to right.
Here is another example:
SELECT *
FROM tbl
WHERE @p1 = 0 OR EXISTS (SELECT *
FROM tbl2
WHERE tbl.col = tbl2.col)
Here, if @p1 is 0 we retrieve all rows from tbl, but if @p1 is 1 only
rows which has a matching row in tbl2 are to be returned. In this example,
SQL Server is actually able to avoid accessing tbl2 if @p1 is 0, since
once @p1 is evaluated, the other branch can be pruned. Note here that
is not your C-style of shortcutting - you get the same result if you
have the condition on @p1 last.
To read more about this topic, I have an article on my web site:
http://www.sommarskog.se/dyn-search.html.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp