December 22, 2010 at 3:31 am
Hi Friends,
Earlier days we using sql2000,
from this server we using mostly Inline query (for join operation)
Eg.
we using Join Operation as
*= and =*
Above one is The current compatibility level is 80.
Later on we migrate the db to sql2005.
Here we change the compatibilty levet to 90.
In this version we use lot of pivot concept.
But here compatibilty levet to 80(Join operation) is not supported.
Can any one please guide me, how to adapt both in one compatible level.
Problem is, we cant change existing join opertaion. because we used around 250 place in my application.
Please guide me, i dont know what do to.
December 22, 2010 at 4:32 am
Short answer: you can't, so you're going to have to make some changes.
Replace all the uses of *= and =* with the new syntax. No-one uses *= and =* any more, for very good reason.
You could rewrite all your PIVOT uses to use alternative syntax (using CASE statements), and stay in 2000 compatible mode, but that would be just delaying the inevitable. See this article by Jeff Moden[/url] for details.
Take the hit, and do the work to improve your application. 250 isn't that many.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 22, 2010 at 4:34 am
This was removed by the editor as SPAM
December 22, 2010 at 4:35 am
If you want to use the newer features (such as pivot) you have no choice but to change the compatibility level to 90 (or later).
The old outer join syntax is however not supported any longer, any there is no alternative but to change to the ANSI syntax of LEFT OUTER JOIN, RIGHT OUTER JOIIN or FULL OUTER JOIN as appropriate.
To be fair Microsoft have documented that support for the old style outer joins would be removed for a good number of years, and the old style outer joins had some very odd behaviours in places.
Mike
December 22, 2010 at 5:08 am
I have small doubt in this,
Right now sql compatibility level is 80.
But in this cross apply and CTE type are accepted. only the problem is pivot type.
Why its not supported
December 22, 2010 at 6:33 am
KMPSSS (12/22/2010)
I have small doubt in this,Right now sql compatibility level is 80.
But in this cross apply and CTE type are accepted. only the problem is pivot type.
Why its not supported
Compatibility levels are there to ensure that you see database behaviour that is compatible with a previous version.
If you set compatibility level 80 on SQL Server 2005, you are still running 2005 code - the 2005 parser, the 2005 optimizer, and so on. Some new features may work, perhaps to a limited extent, others will not.
For example, you can write a recursive CTE and run it under compatibility 80, and you can use APPLY - but not with a function. PIVOT happens to be one of the new 2005 syntaxes that is not usable at all under compatibility 80. In general, you would be unwise to make extensive use of new-version features while running under an old compatibility level.
Compatibility levels are not there to simulate running an earlier version of SQL Server. They provide limited guarantees about the behaviour you will see when running code written against that previous version. The behavioural differences are all documented in Books Online. See http://msdn.microsoft.com/en-us/library/ms178653(v=SQL.90).aspx
The primary reason for compatibility levels to exist is provide some degree of comfort that your existing code will run as it did on a previous version. It is a short-term measure, and you should always update your code to be compatible with the new version's behaviours as soon as possible.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 22, 2010 at 6:38 am
So, my advice is:
1. Remain in 80 compatibility only as long as it takes you to update your code to be fully compatible with 2005.
2. Start using new 2005 features (like APPLY and PIVOT).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply