locked
SQL 2005 Compatibility Level RRS feed

  • Question

  •  

    I am migrating some MSSQL 2000 databases to a MSSQL 2005 machines.

    I am creating a backup on the MSSQL 2000 Server and restoring the backups on the MSSQL2005 Server.

    The MSSQL2005 server databases have the Compatibility Level set to "SQL server 2000(80)".  Are there any performance reasons to set the Compatibility level to "SQL Server 2005 (90) " ?

    If I change this setting some of the SQL stored procedures do not recompile.

    Thanks.

     

     

    Tuesday, May 30, 2006 7:09 PM

Answers

  • Hi,

    you should plan that migration with your software lifecycle. Compatiblity Level below the acual level you are running is always something which *can* slow down your system and *could* not let you use those full features you want to use. Changing this has to be done wisely as your code *could* break after changing the level. I stared the word because it depends on the complexity of your code how much impact this will have.

    More information what you have to keep in mind and where you need to have an eye on can be found in the BOL or at this link: http://msdn2.microsoft.com/en-us/library/ms178653.aspx
     

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Tuesday, May 30, 2006 10:15 PM

All replies

  • I'm moving this thread to the SQL Engine forum, where experts can help.

    Paul

    Tuesday, May 30, 2006 9:41 PM
  • Hi,

    you should plan that migration with your software lifecycle. Compatiblity Level below the acual level you are running is always something which *can* slow down your system and *could* not let you use those full features you want to use. Changing this has to be done wisely as your code *could* break after changing the level. I stared the word because it depends on the complexity of your code how much impact this will have.

    More information what you have to keep in mind and where you need to have an eye on can be found in the BOL or at this link: http://msdn2.microsoft.com/en-us/library/ms178653.aspx
     

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Tuesday, May 30, 2006 10:15 PM