In SQL Server, you can use T-SQL to check the compatibility level of a database. All you need to do is query sys.databases to find the compatibility level for the database in question.
Here’s an example:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'WideWorldImporters';Result:
compatibility_level ------------------- 130
This example returns the compatibility level of the WideWorldImporters database.
Return all Databases
You can also return the compatibility level of all databases, simply by removing the WHERE clause (and adding the name column).
Here’s an example:
SELECT name, compatibility_level
FROM sys.databases;Result:
name compatibility_level ------------------ ------------------- master 140 tempdb 140 model 140 msdb 140 Solutions 140 Music 140 WideWorldImporters 130 world 140
Compatibility Table
Here’s a table outlining the compatibility levels of each product, as well as the compatibility levels that they can be configured for.
| Product | Database Engine Version | Default Compatibility Level Designation | Supported Compatibility Level Values |
|---|---|---|---|
| Azure SQL Database | 16 | 160 | 160, 150, 140, 130, 120, 110, 100 |
| Azure SQL Managed Instance | 16 | 160 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2022 (16.x) | 16 | 160 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2019 (15.x) | 15 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2017 (14.x) | 14 | 140 | 140, 130, 120, 110, 100 |
| Azure SQL Database | 12 | 150 | 150, 140, 130, 120, 110, 100 |
| Azure SQL Database Managed Instance | 12 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2016 (13.x) | 13 | 130 | 130, 120, 110, 100 |
| SQL Server 2014 (12.x) | 12 | 120 | 120, 110, 100 |
| SQL Server 2012 (11.x) | 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.x) | 9 | 90 | 90, 80 |
| SQL Server 2000 (8.x) | 8 | 80 | 80 |
Table source: Microsoft