Post reply

change compatibility level

  • December 11, 2007 at 5:45 am

    #119287

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

  • December 11, 2007 at 7:41 am

    #758965

    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.

  • December 18, 2007 at 1:06 am

    #760910

    Please close topic.

  • December 18, 2007 at 7:06 am

    #760996

    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

  • December 18, 2007 at 7:10 am

    #760999

    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

  • December 19, 2007 at 2:28 pm

    #761583

    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.

  • December 20, 2007 at 3:01 am

    #761683

    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/

  • December 20, 2007 at 2:47 pm

    #761921

    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

  • December 21, 2007 at 12:23 am

    #761990

    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

  • December 21, 2007 at 7:14 am

    #762099

    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

  • December 21, 2007 at 8:24 pm

    #762369

    Thanks Roy!!!

  • December 22, 2007 at 8:24 pm

    #762431

    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.

  • December 23, 2007 at 7:57 am

    #762446

    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)

  • December 24, 2007 at 12:24 am

    #762498

    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

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

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