SQL Server Compatibility level implications

Question

A quick information question :

On my SQL Server 2008, I have databases migrated from SQL Server 2005 and the Compatibility level of the databases on SQL Server 2008 are set to 90 (SQL Server 2005).

What exactly are the implications of that?

It means that the migrated stored procedures, triggers and functions will work well on SQL Server 2008, but are there any disadvantages?

What could be the reason to set the compatibility level to 100 (SQL Server 2008) ?

Performance? Extra features?

Thanx

Answer 1

Compatibility mode is there to help people migrate applications that have functions that are no longer compatible with newer versions of SQL. If you have applications that require functions no longer supported in sql 2008 you would want to run them in compatibility mode; otherwise you would like use a current sql mode. Our business runs an application that due to the way it connects to the database requires SQL 2000 compatibility mode; but it is running on a SQL 2012 server.

Specific functions in code might impact performance as the required logic is different rather than the execution is specifically different. As a very generalised rule SQL code optimised for functions on 2012,2008 would be quicker than SQL 2000 or SQL 7 as they have added things like CTE and un/pivot both of which allow for simplified coding.

Answer 2

Other than inability to use any new features which include behind the scenes ones as the improvements in the Query Optimizer (in SQL Server 2014), staying behind in compatibility mode makes future upgrades more difficult. MSDN has a list of is different between editions http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx