I found an unusual problem between 2000 and 2005 I haven't been able
to decipher from any documentation.

The query structure is as follows:

select *
from
tableA a
join
tableB b ON a.somekey = b.somekey
where
a.type = 'A'
and datediff(yyyy, b.someDateField, getdate()) between
a.lowboundary and a.highboundary

Some basic facts about the elements and data. The low and high-
boundary fields are varchar datatypes. In 2005 (regardless of
compatibility type I run the database under), the query evaluates the
BETWEEN and errors out due to the fact that it is evaluating the
DATEDIFF as an integer and finds a non-integer entry in either
lowboundary or highboundary. I understand and expect this behavior.
Obviously, changing the result of the DATEDIFF function to varchar
allows the operation to go forth.

The odd thing is that there is no "a.type = 'A' " entry, thus the
query wouldn't return anything. In 2000, it seems as though the
engine is evaluating the type = 'A' and short-circuiting and in 2005,
it is trying to evaluate the entire query OR is there an implicit
conversion occuring in 2000 and not in 2005?

As I mentioned, the compatibility mode doesn't change how this reacts,
but running this on a native 2000 server allows this to happen. This
particular code isn't the problem, it's what we might have to contend
with when we migrate this through. Sure, we're going to perform
regression testing, but I'm concerned about what we would miss.

Thanks for any replies.

SQL is a descriptive language rather than a procedural one. You have no
control over the order in which WHERE clause predicates are evaluated. The
SQL Server optimizer is cost-based and may evaluate the conditions in
different query plan operators. The differences between SQL 2000 and 2005
you are simply the result different query plans, most likely because of the
different optimizers. You can verify this by looking at the execution
plans.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<Luke.Schollmeyer (AT) gmail (DOT) com> wrote

(Luke.Schollmeyer (AT) gmail (DOT) com) writes:
To add to Dan's post, there is only one way to control the order of
evaluation, and that is the CASE expression:

AND datediff(uuu, b.someDateField, getdate() BETWEEN
CASE WHEN a.lowboundary NOT LIKE '%[^0-9]%' THEN
a.lowboudnary
END AND
CASE WHEN a.highboundary NOT LIKE '%[^0-9]%' THEN
a.highboudnary
END


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx