| Author |
Topic |
|
mayerl
Yak Posting Veteran
95 Posts |
 Posted - 2010-10-20 : 09:24:47
|
| Morning,I already tried this in development with no issues but I like to make sure before I do it in Production. I changed the compatibility level from 80 to 90 in development so I could use the cross apply function. I wanted to make sure there wasn't any repercussions to changing it back to 80 if I needed to.ThanksLaura |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-20 : 09:42:11
|
What I know...At least you should ask why it is set to 80.Maybe there is an application in production which isn't working properly with the level is set greater than 80?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
mayerl
Yak Posting Veteran
95 Posts |
Posted - 2010-10-20 : 11:15:20
|
| I'll check that out thanks. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-10-20 : 16:37:22
|
| There are no issues with changing the compatibility level up or down. The change is dynamic and can be made without issue.However, you definitely need to verify whether or not there is other code that will break in 90 or higher compatibility mode. You posted in the 2008 forum, so you should have the option to change it to 100 (2008). There are deprecated features that will no longer work in 90 or greater compatibility. For example, old style outer joins will work in 90 compatibility - but do not work in 90 (*= and =*). One issue that caught me was using WITH for table hints is required in certain situations and was not required in 80 compatibility. For example, this works in 80 compatibility but does not work in 90 or greater:SELECT ... FROM tablea (nolock)The above would need to be changed to either of these:SELECT ... FROM tablea WITH (nolock)SELECT ... FROM tablea a (nolock)Other issues I have run into are with linked servers. In 90 and greater, you have to fully qualify the objects referenced through a linked server. For example, the following works in 80 but will not in 90 and greater:SELECT ... FROM linkserver.database..objectThere are other issues - just be prepared to fix them when they are encountered.Jeff |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-20 : 18:09:18
|
quote:
Originally posted by jeffw8713
For example, this works in 80 compatibility but does not work in 90 or greater:SELECT ... FROM tablea (nolock)
sure it does.quote:
Originally posted by jeffw8713
the following works in 80 but will not in 90 and greater:SELECT ... FROM linkserver.database..object
No. this doesn't work in 80 or 90 (or 100) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 07:12:55
|
| "sure it does."Without the "WITH" ?? (I haven't tried it, but I thought that "WITH" had become compulsory).My take is:If you migrate to a newer version of SQL and/or you change Compatibility level you need to do a FULL Regression Test. There are NO guarantees that everything still works the same, so you have to test the application.There is an Upgrade Advisor that you can run. Whilst that may not find everything, for sure anything that it does find you can fix first, rather than finding things one-by-one as you find them in testing.If you have to do a full regression test you might as well upgrade everything to the latest version first - so you get all the benefits in one go, and for only one regression test (rather than doing a regression test to get to Compatibility Mode 90 now, and having to do it again to get to Compatibility Mode 100 in a month's / year's time.)There may be some useful / relevant info about Upgrade Advisor migrating to a new version of SQL / compatibility mode in this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-21 : 12:56:05
|
| Without the "WITH" ?Yes.I agree wholly with everything you say regarding fully testing before making changes to production environment. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 15:40:22
|
| Ah, OK, thanks Russell. I thought I had read a number of posts where people were saying their biggest migration issue was where they had used NOLOCK and some previously optional syntax was now compulsory and that had caught them out. Must have mis-remembered what it was. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-21 : 19:29:45
|
| index hints maybe? i remember issue redgarding WITH keyword too, but can't remember what it is. not locking hints though |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-10-21 : 23:13:01
|
| I can't remember the exact scenario - but, the hints require WITH in 90 and above in certain cases. We ran into that exact issue when we changed the compatibility level.The point is - there are quite a few issues with deprecated code that can cause problems if you don't test everything when changing compatibility level. It's easy enough to change back if there are problems, but you don't want to do that on a production system.Jeff |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-21 : 23:13:59
|
| Agreed! |
|
|
|
|
changing compatibity level