Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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

share|improve this answer
+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:… – brian Nov 14 '12 at 14:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.