In SQL Server, you can use the ALTER DATABASE statement to change the compatibility level of a database.
This can be useful if you have a database that was created in an earlier version of SQL Server, but you now need to use features that are only available with a later compatibility level.
For example, the GENERATE_SERIES() function is available only under compatibility level 160 or higher. While your SQL Server installation may support this compatibility level, you could still have databases that use a lower compatibility level. In this case, if you wanted to use GENERATE_SERIES() against those databases, you’d need to increase the compatibility level to 160 or higher.
Example
Here’s an example of code that changes the compatibility of a database.
ALTER DATABASE Pets
SET COMPATIBILITY_LEVEL = 150;Result:
Commands completed successfully.
The database called Pets now has a compatibility level of 150.
Check your Database Compatibility Level
You can use the sys.databases system catalog view to check the compatibility of a given database, or all databases.
Here’s an example of checking the Pets database.
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';Result:
+-----------------------+ | compatibility_level | |-----------------------| | 150 | +-----------------------+
Compatibility Levels by Product
Here’s a table that shows the compatibility levels supported by each edition of SQL Server and Azure SQL Database.
| Product | Database Engine version | Default compatibility level designation | Supported compatibility level values |
|---|---|---|---|
| Azure SQL Database | 16 | 150 | 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 |
| 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.50.x) | 10.5 | 100 | 100, 90, 80 |
| SQL Server 2008 (10.0.x) | 10 | 100 | 100, 90, 80 |
| SQL Server 2005 (9.x) | 9 | 90 | 90, 80 |
| SQL Server 2000 (8.x) | 8 | 80 | 80 |