SQL Server 2012 (“Denali”) introduces many new features directed towards improving server efficiency and availability and developer/administrator productivity. At the same time, it needs to do away with the past “luggage”. These are features supported by the older versions of SQL Server, that are either no longer in popular use or violate the business rules as T-SQL enhancements made in the newer releases.

Compatibility levels

Every database within Microsoft SQL Server is characterized by something called as the “Compatibility Level”. The compatibility level is primarily used to allow for backward compatibility as it determines the database behaviour with respect to a specific SQL Server version. For example, if you would like to use a SQL Server 2005 specific database behaviour on a SQL Server 2008 R2 instance, you would need to set the compatibility level to “90”.

Trivia: The compatibility level is essentially the major build# of the product. Because SQL Server 2000 was build #80, the compatibility level for SQL Server 2000 databases is 80. Similarly, for SQL Server 2008, the build# is 10.0, and therefore the compatibility level is 100 (compatibility level of a newer release cannot be less than that of the previous releases). For SQL Server 2012 databases, the compatibility level is 110.

Removal of support for COMPATIBILITY_LEVEL 80

SQL Server 2008 and SQL Server 2008R2 were the last versions to support a database compatibility level of 80, i.e. a SQL Server 2000 database. Those who came in late would recall that SQL Server 2005 was a major departure from the architecture of the older SQL Server 2000. This meant that in order to provide backward compatibility support, the SQL Server code would need to carry a lot of additional code which hardly anyone would ever use (in order to leverage the latest features introduced in higher releases, the compatibility level would need to be changed).

In-line with the product’s policy to support 2 prior releases, starting SQL Server 2012, the compatibility level of 80 is no longer supported.

SQL Server 2008 – supported compatibility levels

SQL Server 2012 – supported compatibility levels

Attempting to use T-SQL to change the Compatibility level results in the following error:

~~~ALTER DATABASE AdventureWorks2008R2
SET COMPATIBILITY_LEVEL=80~~~

Msg 15048, Level 16, State 1, Line 1

Valid values of the database compatibility level are 90, 100, or 110.

So, if you are still using a SQL Server 2000 database, please upgrade!

More about the compatibility levels supported by SQL Server 2012 (“Denali”)

To know more about the compatibility levels supported by SQL Server 2012, please visit: http://msdn.microsoft.com/en-us/library/bb510680.aspx

Until we meet next time,

Be courteous. Drive responsibly.