Skip to content

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

Latest commit

 

History

History
95 lines (63 loc) · 3.67 KB

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

File metadata and controls

95 lines (63 loc) · 3.67 KB
title View or Change the Compatibility Level of a Database | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
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 BYHAM
ms.author rickbyh
manager jhubbard

View or Change the Compatibility Level of a Database

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), or SQL Server 2014 (120).

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