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 *=
prosarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
Anthony PerkinsCommented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
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:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21790507.html
And the included link:
http://www.forta.com/blog/index.cfm/2006/1/15/SQL-Server-2005-Outer-Join-Gotcha
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might try to set the database compatiblity mode to sql server 2000 or lower to allow the old syntax...
but again, one should stay away from it.
* it has less functionality when doing multiple outer joins
* it is (IMHO) less readable than the JOIN syntax
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
point split
0
Anthony PerkinsCommented:
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?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would even say that grade B is not really appropriate, as the full answer has been provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.