Does the old outer join syntax work in SQL Server 2005 Beta 2?
-
Hi,
I recently installed SQL Server 2005 Beta 2 and while playing with it I just encountered a problem with one of my old stored procs that uses the old left outer join syntax ( *= ) - it's not returning any rows in beta 2 where it used to return a rowset in SQL Server 2000. Just for info: I had SQL Server 2000 that I wanted to upgrade to SQL Server 2005 beta2 but update was blocked. So I detached all databases, uninstalled SQL server 2000, installed SQL Server 2005 beta 2 and reattached the databases. So far everything has been OK except for this issue. Has anyone else had the same problem? Could it be a problem with the way I did the upgrade or it's an issue in beta 2? Any feedback is much appreciated.
Regards,
Alex
-
I had the same experience. A couple of tools I have from 3rd party vendors don't work anymore because they use the *= join syntax. It looks like the last vestiges of the old Sybase core really are gone in SQL Server 2005. I haven't tried the June CTP but I expect that this is desired behavior rather than a bug.
And then again, I might be wrong ...
David Webb -
Thanks for replying David!
It might be desired to drop the old stuff but there could still be plenty of old code around that would break making many people unhappy
Also I would rather prefer to get a parsing error in this case instead of an empty rowset. I still leave an option open that it can be because of the way I did the upgrade, even though it does look like an issue since you mentioned that you have experienced the same problem. -
I actually get an error message back telling me I have to set the compatibility mode down to 8.0 or lower and that I shouldn't use the '*=' or '=*' syntax because it won't be supported even for compatibility in later releases.
And then again, I might be wrong ...
David Webb -
David,
Thanks for pointing me in the right direction. I checked with BOL and the old outer join syntax is deprecated. I also checked the compatability level on the database and it was set to 80. I set it to 90, reran the query and this time I got a parsing error and the same message as you were getting that the query should be rewritten or the compatability level should be set to 80. This is good. Now I set compatability level back to 80 and rerun the query - and it again returned no rows and no message was printed out. It does seem to be an issue in beta 2.
-
Yes, it sounds like an issue (where 'issue' really means 'bug'). I'm a little surprised this hasn't popped up on the forum before. Maybe everyone else has a newer CTP where it's fixed.
And then again, I might be wrong ...
David Webb -
It's alright. It's still a beta. I am sure it will be fixed in release version.
-
It's not a bug. It's been announced that this syntaxe would not be supported in Yukon. Download the sql server best pratices analyser and run it on your server(s). There's a whole section about upgrade compatibility that will inform you about such pitfalls.
-
I would advice you to uninstall SQL Server 2005 Beta 2 and instead download and install the latest preview version, the June CTP. You will find that a lot has changed since Beta 2, and perhaps this behavior has changed so that you do not just receive an empty resultset in compatibility mode 80. Unless someone else does so first I can check when I have access to my 2005 machine.
But like Remi says this is not only deprecated, non-ANSI-style outer joins are discontinued in SQL Server 2005.
-
Not a bad thing... I've read of many bugs with this syntax on this very board. It was time for it to go
. -
Hmm, actually I might have jumped the gun there. I could not wait until I got home, so I fired up Virtual PC on my laptop at work, where I have April CTP installed. In this version of Books Online non-ANSI syntax outer joins are listed under "Features Not Supported in the Next Version of SQL Server". Strange, I seem to remember having seen them really discontinued in Yukon. So again unless someone else checks June CTP first (the Books Online are downloadable if you are able to install the beta of .Net Framework 2.0), I will check this when I get home.
-
We'll have to wait 'till you get home...
-
I just tried this in April CTP, and interestingly enough I get a third alternative result from what you have gotten above. I do not receive an empty resultset, however I do not either receive the rows that should be returned from the left table with no matches in the right. Here is what I tested:
create table foo (a int)create table bar (b int)
insert into foo values (1)insert into foo values (2)
insert into foo values (3)
insert into bar values (2)insert into bar values (4)
select foo.a, bar.bfrom foo, bar
where foo.a *= bar.b
select foo.a, bar.bfrom foo
left outer join bar on foo.a = bar.b
When I run this in a database with compatibiliy level 80 (if set to 90 then I get the parse error of course) I get these two results back:
a b- -
2 2
(1 row(s) affected)a b- -
1 NULL
2 2
3 NULL
(3 row(s) affected)What query did you test with? Perhaps you have the same behavior, only your query did not have any matches between the two tables at all, and the rows from the left table were not output as in my case?
Anyway, as I said above, I will also check June CTP later and see how this works there.
-
That's exactly the reason I stay way from this syntax...
-
The result from the jury is in! In June CTP this works exactly as we would expect, which means the way it works in SQL Server 2000. The two queries in the example I posted above both return the same three result rows. If executed in a database with SQL Server 2005 compatibility (90) there is still a parse error of course.
And I did remember correctly, non-ANSI outer join syntax is discontinued in SQL Server 2005 according to the June CTP Books Online. As we have seen through the examples in this thread, this is also how it works (i.e. does not work at all) when executed in a SQL Server 2005 database.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply