| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2011-10-24 : 10:39:40
|
| We have a huge mountain of Stored Procedures (but lets not go there). We are still trying to convert the '*=' to Left Outer Join syntax. So we have SQL Server 2005 and 2008 running in compatibility mode SQL Server 2000.What are the benefits of converting the '*=' to Left Outer Join syntax? It seems the only one I can think of is that a future version of SQL Server may not allow it all together. The reason we ask this question, it's not that we don't want to do the conversion - it's that we don't seem to have the resources to do it and there is a lot of risk involved.I once heard that there is a performance issue running in this 2000-compatibility mode. I haven't observed this and since SQL Server generates an execution plan, I don't see where the issue is. Can anyone verify if there is a Performance issue running in 2000-compatibility mode? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:11:00
|
| from maintainence perspective also left outer join, right outer join etc provides more clarity to code than *=,=*------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-25 : 09:22:53
|
| That could be too. But it just seems very risky to migrate with so much code involved. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-10-25 : 11:52:04
|
| It seems to me that you are saying that you have a mountain of code which could soon be obsolete and you are asking if anyone can come up with a valid reason why you need to act on this information. When seen from that perspective, the question answers itself.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-25 : 12:29:38
|
| A few things to consider:- The *=/=* syntax was deprecated since at least SQL Server 7.0, and possibly even 6.5. Hopefully no one is writing new code using that syntax. If they are, stop them.- That syntax will fail in SQL Server 2012, and SQL 2000 compatibility won't be supported. If you intend on upgrading you'll have to fix it.- Changing to outer joins may affect the results, so you'll have to thoroughly test before you can deploy.Best thing to do to get started is run the Upgrade Advisor for SQL Server 2008, it should find all the places where the syntax is used. You can find it here:http://www.microsoft.com/download/en/details.aspx?id=16978There are also software packages that can automatically fix your code. Not sure if Visual Studio does it, but I'm pretty sure one of RedGate's products can fix that. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-25 : 17:10:05
|
| Thankyou. |
|
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-25 : 19:24:48
|
| If you've always used ANSI syntax then you are safe, but if you have any existing code that uses the simplified T-SQL syntax, that code will not run on SQL Server 2005, and the following error message will be returned: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.Fortunately, that message also provides the solution. Using the sp_dbcmptlevel stored procedure you set the backwards compatibility level so that the old style outer joins work. Until you manually fix them all, that is.paul Tech |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-28 : 10:45:57
|
| Thankyou Paul.But aside from the issue that it will likely not be available in a future version of SQL Server and possibly code clarity. Is there any other downside? I can't see a performance issue but I could be wrong. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 11:44:34
|
| I see no point changing it until you have to. Make sure it isn't in new code. Which is what folk have already said. I don't see that Pauls comment about compatibility level has any value; you've said you are already doing that, and others have said that you won't be able to do that, any more, from the next release of SQL, so if your code hasn't been made obsolete by then, and you want to upgrade, you'll have to fix it.You could start a process of refactoring the code in the meantime. Also, having a comprehensive automated regression-test (i.e. build one if you don;t already have one) might be acceptable - the cost is considerable, but for all future rollouts the saving is huge, so its just whether management think that the need to fix these old-style outer joins is the opportunity to do that; the product will need to have an intended long-life to justify it, I expect. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-28 : 13:48:59
|
quote:
Originally posted by X002548
Maybe you can define the extent of your..ummm..problem
Basicly it's a big problem but we are trying to gauge how urgent it is to fix it. It's like anything, you have a bunch of existing problems and new initiatives and to determine which problem or initiative to tackle first. If we had infinite money, we'd hire someone to convert these and I wouldn't have bothered posting. Plus the performance issue. I've heard people say there is a performance benifit to doing this conversion and was curious what you all had to say on that specifically. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-28 : 13:54:04
|
quote:
Originally posted by Kristen
I see no point changing it until you have to.
Thanks. I guess the issue is not as black an white as was previously suggested. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-28 : 13:57:31
|
quote:
Originally posted by X002548
IF..you follow my link, it will tell in which sproc and exactly on what line the non ANSI Code occurs so it will to tell you how many lines of code need to be fixed.
I had a look, you are quite the genius. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-28 : 14:13:37
|
| "I've heard people say there is a performance benifit to doing this conversion and was curious what you all had to say on that specifically."The issue may be that by running a "newer" version of SQL with an "older" compatibility-mode that you are losing out on some performance improvements.Certainly you are losing out on some new syntax which, in turn, means you are not able to take advantage of the new features which may well save development time, and CPU time. But if you are not actively developing for the application that's a moot point too. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-10-28 : 15:23:52
|
quote:
Originally posted by Kristen
"[i]Certainly you are losing out on some new syntax which, in turn, means you are not able to take advantage of the new features which may well save development time, and CPU time. But if you are not actively developing for the application that's a moot point too.
We actually are able to use most new syntax features. Maybe all. even when we run in the compatibility mode. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-10-28 : 17:25:06
|
Robvolk:quote:
Best thing to do to get started is run the Upgrade Advisor for SQL Server 2008, it should find all the places where the syntax is used. You can find it here:http://www.microsoft.com/download/en/details.aspx?id=16978There are also software packages that can automatically fix your code. Not sure if Visual Studio does it, but I'm pretty sure one of RedGate's products can fix that.
Just remember that any dynamic sql will not be considered by any of the tools.My 2 cents on this problem:You are going to have to deal with this sooner or later. The earlier you start the better. Saying that though if you are doing any development then you could take the hits during development -- write some tests and make sure that any new dev work you do that interacts with the old sp's picks up the technical debt of rewriting them.its quite easy to make a mistake converting the syntax -- especially if you no longer know what the sp was meant to do only what it does now.For new development maybe it would be better to write replacement code from scratch where the code interacts with the db. If you need concurrency then make new versions of the sp's until you are fully migrated over.If you do this then maybe also think about setting up a new schema (or schemas) for the new objects to isolate them from the rest of the db.Good luck. Have fun!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
mrmkhokhar
Starting Member
7 Posts |
|
|
|
Benefits of Left Outer Join Conversion?