What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level

 

The Compatibility Level sets some database behaviors to work in accordance with the specified version of SQL Server. For example, for compatibility levels in SQL Server 2008 and earlier, the PIVOT command was supported in the Recursive CTE. But in SQL Server 2008 and higher compatibility levels, the PIVOT command is not supported in the Recursive CTE.

Consider an application that uses a SQL Server 2008 database and has used a recursive cte in a function in the database. As the database administrator, you want to upgrade the database from SQL Server 2008 to SQL Server 2012.

After the upgrade is completed; If the upgraded version supports the compatibility level of the older version, the compatibility level remains. If the compatibility level did not remain old, the application would not work because the PIVOT command was not supported in the RECURSIVE CTE.

Note: If the new version does not support the compatibility level of the older version, compatibility level is automatically set to the lowest compatibility level that the new version supports. Therefore, upgrading the database to several higher versions at once can cause problems. For Example; SQL Server 2005 to 2016. In such cases, you must first upgrade to SQL Server 2012, then to SQL Server 2016.

Even if the Compatibility Level is not upgraded during the SQL Server Upgrade process, I recommend that you upgrade the compatibility level to the compatibility level of the new version after the transition period. I will explain how to change the Compatibility Level in the article.

 

SQL Server Versions and Supported Compatibility Levels:

SQL Server Version Compatibility Level Supported Compatibility Levels
SQL Server 2000 80 80
SQL Server 2005 90 90,80
SQL Server 2008 and SQL Server 2008 R2 100 100,90,80
SQL Server 2012 110 110,100,90
SQL Server 2014 120 120,110,100
SQL Server 2016 130 130,120,110,100
SQL Server 2017 140 140,130,120,110,100

 

 

You can see the compatibility levels of the databases with the help of the following query.

 

How to Change the Compatibility Level of a Database:

Right-click the database and click Properties. You can change the compatibility level from the Options tab as follows.

You should use the latest SSMS version. Because you can see blank compatibility level section in older versions of SQL Server Management Studio.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

2 comments

  1. This is a good explanation, thank you. You mentioned that Pivot which was supported in SQL Server 2008 is not longer supported in 2012 compatibility mode. Do you know where there might be a list of 2008 commands, features, functions that are not supported in 2014 after a move of DBs from 2008 R2 to 2014 or 2016?

Leave a Reply

Your email address will not be published. Required fields are marked *