View or Change the Compatibility Level of a Database

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

This topic describes how to view or change the compatibility level of a database in SQL Server by using SQL Server Management Studio or Transact-SQL. 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).

In This Topic

Before You Begin

Security

Permissions

Requires ALTER permission on the database.

Using 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, click 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 click Properties.

    The Database Properties dialog box opens.

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

    The current compatibility level is displayed in the Compatibility level list box.

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

Using Transact-SQL

To view the compatibility level of a database

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

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

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

To change the compatibility level of a database

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click 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  

See Also

ALTER DATABASE (Transact-SQL) Compatibility Level