Question regarding how SQL 2005 does it's search

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question regarding how SQL 2005 does it's search

Author

Topic

kenchee
Starting Member

49 Posts

Posted - 2008-02-03 : 22:49:54

Hi,
I got a table that has column A, B, C and D. All of the are varchar(255) with column D an integer. Column A and B will specify some data that will determine the value of C which could be a string version of a date or just normal text.

I had this issue with this query below which will select a specific data in column A and B which has C as a string version of a date. This query works in SQL 2000 but no SQL 2005

select
*
from TABLE
where
A = 'some data' and
B = 'Some data' and
C >= '1/1/2007' and
C <= '1/1/2008' and
D in (select someID from ANOTHERTABLE)

I keep on getting "Conversion failed when converting datetime from character string." I made sure that the value in C are all string version of a date. Then when I run this query, it succeded but basically the same query.

select
*
from
(select * from TABLE where A = 'some data' and B = 'Some data') TBL
WHERE C >= '1/1/2007' and
C <= '1/1/2008' AND
D in (select someID from ANOTHERTABLE)

Can someone tell me why is this the case? Is it due to the process in SQL 2005 that does this search differently? Thanks for your help

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-03 : 23:04:31

I would guess there's some data in your table in the records where A <> 'some data' and B <> 'Some data' that doesn't look like a date. When your first query runs it evaluates those rows and throws the error. But because your second query selects from only records where A and B are qualified, you don't get the error. Can you check the C values where A <> 'some data' and B <> 'Some data' and see if something looks odd there?

kenchee
Starting Member

49 Posts

Posted - 2008-02-03 : 23:27:30

Hi,
Yes, if A <> 'some data' and B <> 'some data' there are values in C that are not string version of date. Only thing I'm wondering is why in SQL 2000 it works without a hitch but in SQL 2005 it throws an error?
Thanks

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-04 : 14:39:22

Sorry, I can't really say. But you're right, there must be something different in how SQL 2005 processes. Hopefully someone on the board more knowledgeable than I will know the details.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-04 : 17:56:15

Basically, you were getting lucky with bad code. You do not have control (mostly) over the order in which SQL will evalute your predicates. So, SQL 200 might have filtered out the rows based on A and B before evaluating C. Here is a sample:

DECLARE @Yak TABLE (A VARCHAR(10), B VARCHAR(10), C VARCHAR(10))

INSERT @Yak
SELECT 'A', 'B', '1/1/2008'
UNION ALL SELECT 'A', 'B', '1/2/2008'
UNION ALL SELECT 'C', 'C', 'foo'
UNION ALL SELECT 'A', 'B', '1/3/2008'
UNION ALL SELECT 'A', 'B', '1/15/2008'
UNION ALL SELECT 'C', 'D', 'bar'
UNION ALL SELECT 'A', 'B', '12/8/2008'

SELECT *
FROM @Yak
WHERE C >= '1/4/2008'

-- Fails
SELECT *
FROM @Yak
WHERE C >= CAST('2008-01-02' AS DATETIME)

SELECT *
FROM @Yak
WHERE C >= CAST('2008-01-02' AS DATETIME) AND A = 'A'

Using SQL 2005, you will notice that the first SELECT does not return what you might think it would, but it is correct:

A	B	C
C	C	foo
C	D	bar
A	B	12/8/2008

And the second select will just fail. And the third may or maynot work depending on the way SQL sets up the predicate. On my box it worked becasue it evaluated the A ='A' first. Here is predicate that was selceted by SQL: [A]='A' AND CONVERT_IMPLICIT(datetime,[C],0)>='2008-01-02 00:00:00.000'

kenchee
Starting Member

49 Posts

Posted - 2008-02-04 : 19:05:28

Thanks.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-04 : 20:28:05

quote:
Originally posted by tprupsis

.. there must be something different in how SQL 2005 processes.

If things didn't change under the covers then there would be no advantage in upgrading. As Lamprey said, the optimiser worked one way in 2000 and another in 2005, which is to be expected really. Usually that's all fine - let the software do its thing and don't concern yourself with commonly held myths about how it all works.
Unfortunately one of the rare occasions when you do want that little bit of extra control is when you need to identify and avoid crap data (holding a date in a string is a classic). Like so many things, if you fix your database all this goes away.

kenchee
Starting Member

49 Posts

Posted - 2008-02-04 : 21:29:07

I would agree with you LoztInSpace but this is a third party Vendor apps db. Only thing I can do now is to suggest to them to change it. I'm certainly not allowed to change any of their codes unless I want to void the warranty.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources