SQL Server databases can operate at different compatibility levels, which vary depending on the SQL Server version, and are sometimes useful for ensuring compatibility with older applications. In this article, we will present a guide on how to check and adjust the compatibility levels of SQL Server databases when necessary.

In SQL Server, each database operates at a specific compatibility level determined by the SQL Server version. Different compatibility levels are available for SQL Server databases, and these levels are used to ensure that applications work compatibly with the database.

Each database can have one of these compatibility levels, ensuring compatibility between the database and the SQL Server version.

The table below displays the supported database compatibility levels based on the SQL Server version:

				
					+--------------------+----------------+---------------------+-----------------------------------+
| Version            | Engine Version | Compatibility Level | Compatibility Levels              |
+--------------------+----------------+---------------------+-----------------------------------+
| SQL Server 2022    | 16             | 160                 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2019    | 15             | 150                 | 150, 140, 130, 120, 110, 100      |
| SQL Server 2017    | 14             | 140                 | 140, 130, 120, 110, 100           |
| SQL Server 2016    | 13             | 130                 | 130, 120, 110, 100                |
| SQL Server 2014    | 12             | 120                 | 120, 110, 100                     |
| SQL Server 2012    | 11             | 110                 | 110, 100, 90                      |
| SQL Server 2008 R2 | 10.5           | 100                 | 100, 90, 80                       |
| SQL Server 2008    | 10             | 100                 | 100, 90, 80                       |
| SQL Server 2005    | 9              | 90                  | 90, 80                            |
| SQL Server 2000    | 8              | 80                  | 80                                |
+--------------------+----------------+---------------------+-----------------------------------+

To check SQL Server database compatibility levels, you can use the following T-SQL script:

				
					-- Used to set appropriate database compatibility levels based on SQL Server version.
-- Finds databases that do not use the recommended compatibility level and generates a query to set them to the appropriate level.

-- Retrieves the recommended compatibility level from the 'master' database.
DECLARE @Recommended_Compatibility_Level VARCHAR(3) = (SELECT compatibility_level FROM sys.databases WHERE name = 'master')

-- Instance_Name | Database_Name | State | Compatibility_Level_Mevcut | Compatibility_Level_Onerilen | Query
SELECT
	 SERVERPROPERTY('SERVERNAME') AS [Instance_Name]
	, name AS [Database_Name]
	, state_desc AS [State]
	,'SQL Server ' + CASE (CONVERT(VARCHAR, compatibility_level))
		WHEN '80' THEN '2000 (8.x) (80)'
		WHEN '90' THEN '2005 (9.x) (90)'
		WHEN '100' THEN '2008 (100)'
		WHEN '110' THEN '2012 (110)'
		WHEN '120' THEN '2014 (120)'
		WHEN '130' THEN '2016 (130)'
		WHEN '140' THEN '2017 (140)'
		WHEN '150' THEN '2019 (150)'
		WHEN '160' THEN '2022 (160)'
		ELSE 'Version Not Found'
		END AS [Compatibility_Level_Mevcut]
	,'SQL Server (' + @Recommended_Compatibility_Level + ')' AS [Compatibility_Level_Onerilen]
	,'USE [master] ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = ' + @Recommended_Compatibility_Level AS [Query]
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') -- Excludes system databases.
AND compatibility_level != @Recommended_Compatibility_Level -- Excludes those not using the recommended value.

The sample output of the above T-SQL script could be as follows:

				
					+---------------+----------------+--------+-----------------------+---------------------------------+----------------------------------------------------------------------------+
| Instance_Name | Database_Name  | State  | Compatibility_Level   | Recommended_Compatibility_Level | Query                                                                      |
+---------------+----------------+--------+-----------------------+---------------------------------+----------------------------------------------------------------------------+
| SQLSERVER01   | AdventureWorks | ONLINE | SQL Server 2016 (130) | SQL Server 2022 (160)           | USE [master] ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 160 |
| SQLSERVER01   | Northwind      | ONLINE | SQL Server 2014 (120) | SQL Server 2022 (160)           | USE [master] ALTER DATABASE [Northwind] SET COMPATIBILITY_LEVEL = 160      |
| SQLSERVER01   | TestDB         | ONLINE | SQL Server 2017 (140) | SQL Server 2022 (160)           | USE [master] ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 160         |
+---------------+----------------+--------+-----------------------+---------------------------------+----------------------------------------------------------------------------+

This query provides the current compatibility level of each database, the recommended compatibility level, and a SQL query that can be used to adjust the compatibility level if necessary.

In this blog post, we learned how to check and adjust SQL Server database compatibility levels. By using appropriate compatibility levels, you can ensure smooth operation of your applications and keep older applications up to date. Regularly checking compatibility levels can enhance the performance and reliability of your database.