Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
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?
Here you can find the difference between compatibility levels.
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017