View or change the compatibility level of a database

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database

This article describes how to view or change the compatibility level of a database in SQL Server by using SQL Server Management Studio or Transact-SQL.

Important

Before you change the compatibility level of a database, you should understand the impact of the change on your applications. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

Permissions

Requires ALTER permission on the database.

Use SQL Server Management Studio

To view or change the compatibility level of a database:

  1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, select the server name.

  2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

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

    The Database Properties dialog box opens.

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

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

Use Transact-SQL

View the compatibility level of a database

  1. Connect to the Database Engine.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example returns the compatibility level of the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks2012';  
GO

Change the compatibility level of a database

  1. Connect to the Database Engine.

  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 AdventureWorks2012 database to 120, which is the compatibility level for SQL Server 2014 (12.x).

ALTER DATABASE AdventureWorks2012  
SET COMPATIBILITY_LEVEL = 120;  
GO

Next steps