Remember that when none of the items
in the CASE expression yields true and
there's no ELSE element, the CASE expression uses an implicit ELSE NULL element.
This particular CASE expression converts val
to an integer when val contains only digits.
The logical expression T1.val NOT LIKE
'%[^0-9]%' is simplistic and doesn't cover all
cases of valid or invalid integers. But again,
this is just an example. You can enhance this
CASE expression to make it more robust and
more accurate in terms of verifying that val is
convertible to an integer.
As I mentioned earlier, I consider the
discrepancy between physical and logical
query-processing order to be a bug, but in
the same breath I should say that I consider
it an acceptable bug. The other option is to
compromise performance, which is a less
acceptable option than having the query fail,
in this case. At least, when a failure occurs,
you can take steps to avoid it by using one
of the methods I've discussed.
Another situation is somewhat similar,
but you shouldn't consider it a bug since
it doesn't conflict with standard SQL. To
demonstrate this condition, run the code in Listing 3 to create the table T1 and populate it with sample data. Look at the following
query and before you run it, see if you can
tell whether or not it should fail:
SELECT keycol, val
FROM dbo.T1
WHERE val * 0.5 >= 1
AND 10 / val <= 5;
Now run the query. When I ran this query in
SQL Server 2005, I got the following divide by-zero error: Msg 8134, Level 16, State 1,
Line 1, Divide by zero error encountered.
Many programming languages (such as C) physically evaluate logical expressions
from left to right and short-circuit as soon as
the result is known. For example, when val is
zero, the expression val * 0.5 >= 1 is false, so
there's no reason to evaluate the next expression. Had SQL worked the same way, such a
query shouldn't have failed. But SQL differs
from other programming languages in many
ways. Each phase in logical query processing
is considered an all-at-once operation. Thus,
SQL Server logically processes all logical
expressions in the WHERE phase at the
same time. In physical terms, SQL Server
does support short-circuits, but it's free to
evaluate the expressions in a particular phase
in any order that it deems fit. The physical
order of evaluation isn't necessarily left to
right, rather it's driven by cost estimations.
This is why I don't think that a failure in
such a case can be considered a bug.
Can you do anything to resolve the issue?
Yes, by using a similar technique to the one
I showed in the previous scenario: a CASE
expression like the one in Listing 4, in which
you have full control over the order of evaluation. This query runs with no failure and
returns the result set that Table 2 shows.
Circumvent Conflicts
You should be aware that in SQL Server
2005, physical query processing isn't necessarily in agreement with logical query processing for the sake of performance—even
when this disparity causes query failure.
However, in cases when you do get a query
failure, you should reexamine the design
of your tables. If you can't change their
design, you can use CASE expressions to
control the order in which the optimizer
evaluates query elements for processing.
Also, remember the all-at-once concept.
Logical expressions aren't necessarily evaluated physically from left to right. But in
scenarios where you need to control the
order of evaluation, you can do so by using
CASE expressions.
End of Article
Prev. page
1
[2]
next page -->
