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!