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.
|
1 |
select name,compatibility_level from sys.databases |
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.
