Tip: Use of *= and =* are not supported in SQL 2005 compatibility 90 databases

Tip: Use of *= and =* are not supported in SQL 2005 compatibility 90 databases

  
Symptoms:

During a SQL migration process from SQL 2000 (or earlier) to SQL 2005 it's usual to set the compatibility level of a database to 90 in the SQL server 2005 to take advantage of higher performance and availability. Unfortunately, this could generate some unexpected runtimes errors like the one with the following message:

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Cause:

This only happens if there are any advanced query that uses the deprecated syntax '*=' for LEFT OUTER JOIN or '=*' or RIGHT OUTER JOIN, and usually it's only detected in runtime, which can have a high impact in a production environment. The issue here is that this syntax for the OUTER JOINs has long been considered deprecated, but only now, with SQL 2005 and database in compatibility level 90, became no longer supported, as stated in the Microsoft article http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Resolution:

Setting the compatibility level to 80 in the SQL 2005 database solves this problem, however it will not gain from the benefits of performance and availability of the compatibility level 90.
The most logical resolution, and the recommended, is to redo the advanced queries in order to avoid using this deprecated syntax.

Best regards

Miguel João