Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
This article describes how to view or change the compatibility level of a database in SQL Server, Azure SQL Database, or Azure SQL Managed Instance by using SQL Server Management Studio or Transact-SQL.
Before you change the compatibility level of a database, you should understand the effect of the change on your applications. For more information, see ALTER DATABASE compatibility level.
The code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
Requires ALTER
permission on the database.
To view or change the compatibility level of a database using SQL Server Management Studio (SSMS)
Connect to the appropriate server or instance hosting your database.
Select the server name in Object Explorer.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Note
You can't modify the compatibility level of system databases in Azure SQL Database.
Right-click the database, and then select Properties.
The Database Properties dialog box opens.
In the Select a page pane, select Options.
The current compatibility level is displayed in the Compatibility level list box.
To change the compatibility level, select a different option from the list. The available options for different Database Engine versions are listed in the ALTER DATABASE Compatibility Level (Transact-SQL) page.
You can use Transact-SQL to view or change the compatibility level of a database using SSMS or Azure Data Studio.
Connect to the appropriate server or instance hosting your database.
Open a New Query.
Copy and paste the following example into the query window and select Execute. This example returns the compatibility level of the AdventureWorks2022
sample database.
USE AdventureWorks2022;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'AdventureWorks2022';
GO
Connect to the appropriate server or instance hosting your database.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example changes the compatibility level of the AdventureWorks2022
database to 160
, which is the compatibility level for SQL Server 2022 (16.x).
ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO
Note
The compatibility level of a Fabric SQL database cannot be changed, and always uses the latest version.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Evaluate strategies for migrating to Azure SQL - Training
Evaluate strategies for migrating to Azure SQL
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Documentation
ALTER DATABASE compatibility level (Transact-SQL) - SQL Server
Sets Transact-SQL and query processing behaviors to be compatible with the specified version of the Database Engine.
sp_dbcmptlevel (Transact-SQL) - SQL Server
sp_dbcmptlevel sets certain database behaviors to be compatible with the specified version of SQL Server.
Upgrade compatibility level degrades performance - SQL Server
This article provides resolutions for the problem that occurs when you upgrade database compatibility level from 120 to 130.