Home / server / Questions / 445987
In Process
Iain Hoult
Asked: 2012-11-07 08:03:11 +0800 CST

Is there a negative impact to using SQL Server with a lower compatibility level

  • 772

We have SQL Server 2008 R2 which we are using for a database with a compatibility level set as 2005 (90).

We will by upgrading it to 2008 at some point, but in the meantime I would like to know if having the database in a lower compatibility level is this going to have a negative effect on its performance?

I found this question that implies there could be a negative effect, but it's for an older version of SQL Server.

sql-server

1 Answers

  1. Running the database with an older compatibility level should not negatively affect performance.

    However, there may be other side effects. I'm reminded of an issue where one of our queries was using the TOP 100 PERCENT trick when running on SQL 2000, and upon upgrade to 2005 (w/2000 compatibility mode) that query broke due to the SQL team fixing the "quirk".

    The best thing to do is run the Database Upgrade Advisor to ensure everything will work as expected. If you get a clean bill of health from that, you should find no drawbacks to running in compatibility mode (apart from not being able to use new 2008R2 features).

    • 2