Link to home
Create AccountLog in
Avatar of prosar
prosarFlag for United States of America

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 *=
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

not that I know of. you might check the books online if you see something else:
http://msdn2.microsoft.com/en-us/library/ms173545(SQL.90).aspx
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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.
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
I would even say that grade B is not really appropriate, as the full answer has been provided.