prosar
asked on
Does SQL2005 support the use of legacy transact-sql outer joins such as =* and *=
Does SQL2005 support the use of legacy transact-sql outer joins such as =* and *=
It depends on your definition of support. Does the old style still work? Than the answer is yes. Would I use use or recommend using? Than the answer is no.
Although not referenced in BOL that I can find, the join does run in 2K5, as acperkins has indicated. But get away from them -- I think I read somewhere that it will *not* be supported in the next release of SQL Server.
I had tested the old syntax successfully in SQL Server Express, however it looks like that may not hold up in SQL Server 2005. See here:
https://www.experts-exchange.com/questions/21790507/SQL-Server-2005-Outer-Joins-and-SQL-Express.html
And the included link:
http://www.forta.com/blog/index.cfm/2006/1/15/SQL-Server-2005-Outer-Join-Gotcha
https://www.experts-exchange.com/questions/21790507/SQL-Server-2005-Outer-Joins-and-SQL-Express.html
And the included link:
http://www.forta.com/blog/index.cfm/2006/1/15/SQL-Server-2005-Outer-Join-Gotcha
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Actually, on double-checking the Northwind database I was using in SQL Server Express was in fact using a Compatibility Level of 80, as soon as I changed it to 90 I got the infamous:
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.
So angelIII is absolutely right.
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.
So angelIII is absolutely right.
point split
prosar,
You may have overlooked my comment: "So angelIII is absolutely right."
See here from the EE Help:
I accepted the wrong answer. Now what?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
You may have overlooked my comment: "So angelIII is absolutely right."
See here from the EE Help:
I accepted the wrong answer. Now what?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
I would even say that grade B is not really appropriate, as the full answer has been provided.
http://msdn2.microsoft.com/en-us/library/ms173545(SQL.90).aspx