Skip to content

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

Latest commit

 

History

History
101 lines (71 loc) · 4.22 KB

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

File metadata and controls

101 lines (71 loc) · 4.22 KB

title: "View or Change the Compatibility Level of a Database | Microsoft Docs" ms.custom: "" ms.date: "11/24/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "databases" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "article" helpviewer_keywords:
  • "compatibility levels [SQL Server], viewing"
  • "compatibility [SQL Server], databases"
  • "compatibility levels [SQL Server], changing" ms.assetid: 579867ec-57cb-4cb8-af35-9688c1e9e15d caps.latest.revision: 20 author: "stevestein" ms.author: "sstein" manager: "craigg" ms.workload: "Active" monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"

View or Change the Compatibility Level of a Database

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md] This topic describes how to view or change the compatibility level of a database in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. 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 [!INCLUDEssDEnoversion], 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 choices are SQL Server 2008 (100), SQL Server 2012 (110), SQL Server 2014 (120), SQL Server 2016 (130), and SQL Server 2017 (140).

Using Transact-SQL

To view the compatibility level of a database

  1. Connect to the [!INCLUDEssDE].

  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 [!INCLUDEssSampleDBobject] 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 [!INCLUDEssDE].

  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 [!INCLUDEssSampleDBobject] database to 120, which is the compatibility level for [!INCLUDEssSQL14].

ALTER DATABASE AdventureWorks2012  
SET COMPATIBILITY_LEVEL = 120;  
GO

See Also

ALTER DATABASE (Transact-SQL) Compatibility Level