Hello! We found a problem when migrating from SQL Server 2005 to SQL Server 2008. SQL-statements like
SELECT <table1>.<fields>, <table2>.<fields>, <table3>.<fields>
ON <table2>.<field> = <table1>.<field>
LEFT JOIN <table3>
ON <table3>.<field> = <table1>.<field>
WHERE (<table2>.<field> = 2 AND <table2>.<another_field> = 'some_text'
OR <table3>.<field> = 2 AND <table3>.<another_field> = 'some_text')
give different results on 2005 / 2008. On 2008 I get lots of exessive rows which have NULLs instead of the values required in the WHERE-term.
Changing the Compability Level does nothing. It also doesn't matter from where the query is executed (direct via ODBC, in a view or from the Enterprise Manager).
2005/2008 also use different execution plans. (Btw: how can I make an screenshot of a EP larger than the screen?)
After some hours I drilled it down to the existence of a index! By deleting or deactivating the index 2008 gives the correct results like the 2005 did, after activating the index I get these exessive NULL-rows again!
Anyone else ran into this problem? Any tips (dropping all indexes in a 25+ GB DB is NOT an option :-) ?
Thanks in advance!
Holger WellenkÃÂ¶tterMCTS SQL 2005
View Complete Post