Evil Kraig F (9/25/2012)
antonio.estima 4150 (9/25/2012)
Now, the filter "where SUBSTRING (nomeint, 1,1) = 'P' ", which is in subquery is precisely to bring unit not equals "PA" and thus does not occur conversion problem, because the field b.unit is of type varchar and w.unidade is of type int.Your query is executing in a different pattern in the two engines. WHERE clause does not fire prior to joins or any other operation. The entire statement is taken as a whole once you're done and works from there.
You got lucky for a long time in 2k5 and this just never altered the order in the engine on you. You cannot guarantee the WHERE clause firing first without using a few tricks. For an example of the most frustrating version of this process, do a google search on WHERE ISNUMERIC() error. It's quite common in EAV builds too.
This.
The order that a query is processed is not guaranteed. The where clause is not necessarily processed left to right, or right to left, or any other order. It depends on the exec plan created for the query.
Short version, you got lucky on SQL 2000, you need to go and fix your code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability