1

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.

1 Answer 1

2

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).

1
  • +1 Justin Also don't forget that when running in compatibility mode 2000 (80), you'll have to use a slight hack to get some dmvs to work properly: sqlskills.com/blogs/paul/post/…
    – brian
    Nov 14, 2012 at 14:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .