Different behavior in 2000 and 2005 for same query

Hi there,

We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.

To reproduce the issue you can do the following

Create this structure in a 2000 and 2005 server instances

CREATE TABLE test

(a int,

b varchar(30))

INSERT INTO test

(a, b)

VALUES (1, '2')

INSERT INTO test

(a, b)

VALUES (3, '3a')

INSERT INTO test

(a, b)

VALUES (4, '4')

Then run the following statement in both servers:

UPDATE test

SET a = ltrim(rtrim(b))

WHERE b NOT LIKE '%a%'

AND ltrim(rtrim(b)) <> a

In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:

Msg 245, Level 16, State 1, Line 20

Conversion failed when converting the varchar value '3a' to data type int.

By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.

I know fixing this instance itself is easy but I’m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?

Any guidance is much appreciated.

Thanks!

You could change the database Compatibility level to 80 (SQL 2000) and determine if that helps.

There is a significant difference between the versions query processing.

And SQL 2005 allows less implicit conversions (aka, careless programming that often contravenes security).

I suspect you will need to make the changes to avoid implicit conversions as soon as possible.