[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Does SQL2005 support the use of legacy transact-sql outer joins such as =* and *=

Posted on 2006-03-27
11
Medium Priority
?
428 Views
Last Modified: 2008-02-01
Does SQL2005 support the use of legacy transact-sql outer joins such as =* and *=
0
Comment
Question by:prosar
  • 4
  • 4
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16300427
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16300435
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16301528
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16302345
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 16303914
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16307498
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16781991
point split
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16784828
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16784843
I would even say that grade B is not really appropriate, as the full answer has been provided.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question