change compatibility level

Question

change compatibility level

Mark Hemelrijk

Old Hand

Points: 394
More actions
December 11, 2007 at 5:45 am

#119287

What is the impact of changing the compatibility level from 80 to 90?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Clive Strong SSChampion Points: 11222 More actions · Answer 1

I don't believe there is any impact. I've not experienced any problems personally.

Changing compatibility will open up SQL2005 only features though. I'd restore a version to a test database, change the compatibility and make sure the result sets are identical.

Mark Hemelrijk Old Hand Points: 394 More actions · Answer 2

Mark Hemelrijk

Old Hand

Points: 394

December 18, 2007 at 1:06 am

#760910

Please close topic.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

I have seen it where tsql code breaks because of compatibility level. Some code that works on 2000 is not compatible with 2005 because of syntax.

My blog: http://jahaines.blogspot.com

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

our devs would write code in 2000 which would look like

select col1, col1 from some_table order by col1

why they did this, no one knows. but it broke in sql 2005 and they had to change it

Bulent Gucuk SSCertifiable Points: 5497 More actions · Answer 5

Before upgrading to SQL 2005 running the SQL Server 2005 Upgrade advisor inspects the databases and stored proc codes to find issues that should be resolved before/after or anytime. Like older sytle joins, or column aliases in the order by clause can not be prefixed by the table aliases.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

the reporting capability doesn't work when the database is in 2000 mode - I consider this a disadvantage so chnaging to 9.0 allows better database reporting.

As mentioned it doesn't check your code so things may or may not break. I'm not totally sure the wizard gets it right either.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

ksullivan SSCrazy Points: 2346 More actions · Answer 7

See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm

Which is SQL Server 2005 Books Online for

sp_dbcmptlevel (Transact-SQL)

and Lists Behavioral Differences Between Level 60 or 65 and Level 70, 80, or 90

Hemant Sengar-524282 Mr or Mrs. 500 Points: 586 More actions · Answer 8

Hi all,

I have restored my SQL 2000 database onto SQL 2005 with the compatibility level remain 80. Now, I would like to switch the compatibility level fro 80 to 90 to ustilise the advanced functionality of 2005, but I am not aware of the fact that how it will affect my database? Will some sql code stopped functioning after switching?

Are there some testing scripts available to check whether everything is allright after the switch over from 80 to 90?

OR

How can I test on my own to verify this?

Best Regards,

Hemant Sengar

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

If you have third party applications running, you should never try to change the compatibilty level to 90 without proper testing. You could run the upgrade advisor to find out if there will be issues when moving, but still I would get a set up on QA and test it throughly.

Just my 2 cents

-Roy

Hemant Sengar-524282 Mr or Mrs. 500 Points: 586 More actions · Answer 10

Hemant Sengar-524282

Mr or Mrs. 500

Points: 586

December 21, 2007 at 8:24 pm

#762369

Thanks Roy!!!

Ian Yates SSCoach Points: 19738 More actions · Answer 11

The BIG problem I have with 90 compatibility level is

1. I still code and design using SQL 2000.

2. The EM database diagram designer in SQL 2000 generates lovely code, but it uses some locking hints

3. In the link given by another user to the 2005 BOL page (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm), the part that says

80

For locking hints in the FROM clause, the WITH keyword is always optional.

90

With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).

Causes a LOT of trouble 🙂 Apart from that 90 compatibility level is fine from everything I've seen. IMHO, the SQL 2000 designer should have the WITH keyword there, much like having INNER JOIN rather than just JOIN.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

Good to see I'm not the only one stuck with 7 year old software at work, Ian. To keep up though, I finally broke down and installed the Dev Edition of 2k5 on my home machine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 13

Jeff Moden (12/23/2007)
... I finally broke down and installed the Dev Edition of 2k5 on my home machine.

Now .... unleash the beast :w00t::smooooth:

btw: No problems overhere after switching db_level 90 except for some dev-s still trying to use the depricated "old school" outer joins (*= =*)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me