View or Change the Compatibility Level of a Database

This topic describes how to view or change the compatibility level of a database in SQL Server 2012 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

• To view or change the compatibility level of a database, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Security

Permissions

Requires ALTER permission on the database.

[Top]

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 choices are SQL Server 2005 (90), SQL Server 2008 (100), or SQL Server 2012 (110).

[Top]

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 110, which is the compatibility level for SQL Server 2012.

ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 110;
GO