SQL Server - Convert 2000 Database to 2008

Hi, I restored a sql2000 database in sql2008 and set compatibility level to
100.
Next executed DBCC UPDATEUSAGE (0) and DBCC CHECKDB. Ansi_nulls is Off.

Now, my problem is in a update trigger which contains an instruction like
this:

select Value
from Inserted I inner join
Deleted D ON I.Id=D.Id left join
Invoices inv ON I.IdInvoice = inv.Id
where inv.SomeValue > 1

Now I know that it isn't a great query but it's just an example.
The weird thing is that when this query executes and there is NO Record in
the table Invoices to match with I.IdInvoice, so inv.Somevalue is Null, the
query returns the record being updated. Something Like null >1= True?

Even stranger is that I have a similar Database en sql2005(Comp. Level 80)
which after restoring it in sql2008 and doing the same steps works CORRECTLY.

Any ideas?

When you have a LEFT OUTER join and you place a predicate involving the
right side table in the WHERE clause, this turns the join effectively to
inner join (because the predicate filter out the NULL values). In the
example query you posted, you are asking to retrieve rows where you have
match in Inserted/Deleted/Invoices and SomeValue is > 1. That will
return all updated rows (as only those should exists in all 3 tables
given you did not update the primary key) where SomeValue > 1.

To make the query correct you have to move the WHERE predicate to the
JOIN conditions:

SELECT Value
FROM Inserted AS I
INNER JOIN Deleted AS D
ON I.Id=D.Id
LEFT JOIN Invoices AS inv
ON I.IdInvoice = inv.Id
AND inv.SomeValue > 1;

--
Plamen Ratchev
http://www.SQLStudio.com

Thanks for your response.

I know that the query isn´t well written. The thing that troubles me is that
the same query returns diferent results in a DB restored from sql2005 and a
DB restored from sql2000. With there compatible level set to 100.

Why do you have ANSI_NULLS off? That could be the reason (if one
statement is executed with OFF and another with ON you can get different
results). Also, note this statement from SQL Server 2008 BOL:

applications that explicitly set the option to OFF will generate an
error. Avoid using this feature in new development work, and plan to
modify applications that currently use this feature."

--
Plamen Ratchev
http://www.SQLStudio.com

Good question. W'll change it when upgrading to 2008. In my case both DBs
have the same options.

More reason to do so as soon as possible.