Post reply

chnageing Compatibility level in SLQ SERVER 2008

  • April 21, 2011 at 1:05 pm

    #249203

    Hi,

    We have plan to migrate from SQL SERVER 2000 to SQL SERVER 2008.We want to set the compatibility mode to 80(to support our existing code).Will this setting effects on th performance or the engine itself or just simply it will let us to use SQL SERVER 2000 syntaxes?

    Thanks

  • April 21, 2011 at 1:23 pm

    #1314687

    Aspet Golestanian Namagerdi (4/21/2011)

    Hi,

    We have plan to migrate from SQL SERVER 2000 to SQL SERVER 2008.We want to set the compatibility mode to 80(to support our existing code).Will this setting effects on th performance or the engine itself or just simply it will let us to use SQL SERVER 2000 syntaxes?

    Thanks

    if you change youre comp lvl it will make sure that depcrecated commands/code ect will not be supported anymore. So "Old" code will not work anymore.

    And it will not let you use SQL Server 2000 syntaxes (depcrecated in higer lvl) in comp mode 80 because this is SQL 2005. comp mode 70 is for SQL 2000

  • April 21, 2011 at 1:45 pm

    #1314697

    Thanks for your reply.

    But setting the compatibility level to SQL SERVER 2000(80) does it have any effect on Performance and Operation of engine itself.

  • April 21, 2011 at 2:05 pm

    #1314706

    Short answer: No

    In theory there can be if you go back from 90 to 80. check http://msdn.microsoft.com/en-us/library/bb510680.aspx you can see that Comparisons between numeric types on 90 can be faster then in 80.

    But you come from 80 mode and stays in 80 mode so you wont have any problems there.

    PS sorry for failing in my previous post you are correct with 80 is sql 2000 and 90 and 2005.

  • April 22, 2011 at 5:24 am

    #1314840

    Hi

    non-ANSI joins will not work until unless you set the database compatibility level

    non-Ansi outer join operators are " =* " and " *= "

    Ali
    MCTS SQL Server2k8

  • April 22, 2011 at 6:27 am

    #1314849

    Marco V (4/21/2011)

    And it will not let you use SQL Server 2000 syntaxes (depcrecated in higer lvl) in comp mode 80 because this is SQL 2005. comp mode 70 is for SQL 2000

    Compat mode 70 is SQL 7. Compat mode 80 is SQL 2000 (SQL 8), compat mode 90 is SQL 2005 (SQL 9)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 22, 2011 at 6:47 am

    #1314853

    GilaMonster (4/22/2011)

    Marco V (4/21/2011)

    And it will not let you use SQL Server 2000 syntaxes (depcrecated in higer lvl) in comp mode 80 because this is SQL 2005. comp mode 70 is for SQL 2000

    Compat mode 70 is SQL 7. Compat mode 80 is SQL 2000 (SQL 8), compat mode 90 is SQL 2005 (SQL 9)

    yeah youre correct gila but had it already rectified it in my previous post. 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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