Does the compatibility level affect database improvements?

Question

I am using SQL Server Express 2008 R2. I have a database with compatibility level set to: SQL Server 2005.

1. Are there improvements in 2008 R2 model?
2. Does compatibility level set to 2005 affects database in any way?

 

Answer 1

Hi,

The compatability level does NOT affect the way the database engine works in terms of the major improvements between the versions. What the compatability level sets is the behaviour of certain database commands.

Settings the compat level to 90 (SQL 2005) will only have an affect on certain database features that have changed between 2005 and 2008. However the database still runs and works under the 2008 engine.

http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-13-Difference-between-database-version-and-database-compatibility-level.aspx

 

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Hi,

Compatibility level of databaes allows you to some spartial backward compatibility only with earlier versions of SQL Server. The New functionality might work under older compatibility levels.

"New functionality might work under older compatibility levels"

For example if you are running SQL Server 2005, If one of the database is running with compatibility with 80 (means sql server 2000 compiatability), it means the particular database will not use all the existing features of SQL Server 2005.

check the below article to know more about compatabilities:
http://msdn.microsoft.com/en-us/library/bb510680(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms178653(v=SQL.90).aspx

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 3

Let me clear.

I want to know whether the 2008 R2 model is more efficient that the 2005 model? If the compatibility level is set to the older version, then probably, 2008 R2 may not use the benefits of the new file system.

Answer 4

The compat level will have little to no affect on the performance of the system. It only affects the way some functions work in terms of compatibility the performance improvements in the engine will be applied to the database no matter which compat level you are running but in general unless you have reason not to you should update the compat level when you upgrade the engine.

You should test your application first however the chances of any major problems is generally low.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog or Twitter.
Please click the Mark as Answer button if a post solves your problem!