Skip to content

view-or-change-the-compatibility-level-of-a-database.md

Latest commit

 

History

History
98 lines (64 loc) · 4.31 KB

view-or-change-the-compatibility-level-of-a-database.md

File metadata and controls

98 lines (64 loc) · 4.31 KB
title View or Change the Compatibility Level of a Database
description Learn how to view or change the compatibility level of a database in SQL Server or Azure SQL by using SQL Server Management Studio or Transact-SQL.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer randolphwest
ms.date 04/22/2025
ms.service sql
ms.subservice supportability
ms.topic how-to
ms.custom
ignite-2025
helpviewer_keywords
compatibility levels [SQL Server], viewing
compatibility [SQL Server], databases
compatibility levels [SQL Server], changing
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

View or change the compatibility level of a database

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

This article describes how to view or change the compatibility level of a database in [!INCLUDE ssnoversion], Azure SQL Database, or Azure SQL Managed Instance by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

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.

[!INCLUDE article-uses-adventureworks]

Permissions

Requires ALTER permission on the database.

Use SQL Server Management Studio

To view or change the compatibility level of a database using SQL Server Management Studio (SSMS)

  1. Connect to the appropriate server or instance hosting your database.

  2. Select the server name in Object Explorer.

  3. 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.

  4. Right-click the database, and then select Properties.

    The Database Properties dialog box opens.

  5. In the Select a page pane, select Options.

  6. 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 [!INCLUDE ssDE-md] versions are listed in the ALTER DATABASE Compatibility Level (Transact-SQL) page.

Use Transact-SQL

You can use Transact-SQL to view or change the compatibility level of a database using SSMS.

View the compatibility level of a database

  1. Connect to the appropriate server or instance hosting your database.

  2. Open a New Query.

  3. Copy and paste the following example into the query window and select Execute. This example returns the compatibility level of the [!INCLUDE sssampledbobject-md] sample database.

    SELECT compatibility_level
FROM sys.databases
WHERE [name] = 'AdventureWorks2022';
GO

Change the compatibility level of a database

  1. Connect to the appropriate server or instance hosting your database.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example changes the compatibility level of the [!INCLUDE sssampledbobject-md] database to 160, which is the compatibility level for [!INCLUDE sssql22-md].

    ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 160;
GO

Note

The compatibility level of a SQL database in Fabric cannot be changed, and always uses the latest version.

Related content