473,225 Members | 1,311 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

2000 to 2005 query processng difference?

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.

Mar 8 '07 #1
2 4833
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?
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

<Lu**************@gmail.comwrote in message
news:11**********************@c51g2000cwc.googlegr oups.com...
>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.
Mar 8 '07 #2
(Lu**************@gmail.com) writes:
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?
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, es****@sommarskog.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
Mar 8 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Andrew Mayo | last post by:
There is something very strange going on here. Tested with ADO 2.7 and MSDE/2000. At first, things look quite sensible. You have a simple SQL query, let's say select * from mytab where col1 =...
2
by: Benoit Le Goff | last post by:
Hello. I test some query on sql server 2000 (sp2 on OS windows 2000) and i want to know why a simple query like this : select * from Table Where Column like '%value' is more slow on 2000 than...
9
by: RvGrah | last post by:
After much hair-pulling, I've finally found the answer to a problem that many are fighting with, difficulty connecting from Sql 2005 Server Management or VS2005 to a remote Sql Server running Sql...
2
by: thomas_vicker | last post by:
Hi, I need to maintain both SQL Server 2000 and 2005 on my PC for support purposes. I am using the developer additions. After I install everything (with no install errors) I can use SQL 2005 Mgt...
5
by: Easystart | last post by:
Hi, Sorry for my English. English is not my native tougue. I am working in MS Access 2000 with a SQLServer 2000 Backend database. MS Access 2000 is my GUI front end that has SQLServer linked...
3
by: Martini | last post by:
Hello all. I have quite disturbing situation where I am not happy about the way how SQL handles the query. Situation is related to using user function in INNER JOIN select. Although the problem...
1
by: Alex | last post by:
Hi Everyone, Most of our MS SQL Servers are still running on SQL 2000, but being I will soon be upgrading my workstation to Vista Business I'd like to install MS SQL 2005 Enterprise Manager to...
11
by: HC | last post by:
I posted this in one of the VB forums but I'm starting to think it might be more appropriate to have it here, since it really seems to be a SQL server (MSDE/Express 2005) problem: Hey, all, I...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.