Skip to content

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

Latest commit

 

History

History
94 lines (64 loc) · 4.33 KB

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

File metadata and controls

94 lines (64 loc) · 4.33 KB
title View or Change the Compatibility Level of a Database | Microsoft Docs
description Learn how to view or change the compatibility level of a database in SQL Server by using SQL Server Management Studio or Transact-SQL.
ms.custom
ms.date 11/24/2017
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.technology supportability
ms.topic conceptual
helpviewer_keywords
compatibility levels [SQL Server], viewing
compatibility [SQL Server], databases
compatibility levels [SQL Server], changing
ms.assetid 579867ec-57cb-4cb8-af35-9688c1e9e15d
author stevestein
ms.author sstein
monikerRange =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

View or Change the Compatibility Level of a Database

[!INCLUDE SQL Server Azure SQL Database] This topic describes how to view or change the compatibility level of a database in [!INCLUDEssnoversion] 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 available options for different [!INCLUDEssde_md] 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 [!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