View or Change the Compatibility Level of a Database

Compatibility for SQL Server 2005 (90), SQL Server 2008 (100), or SQL Server 2012 (110).

The first thing that you need to do is to check the compatibility level that your database is running under.  As mentioned above any database that is upgraded using the backup and restore or detach and attach method will not change the compatibly level automatically, so you will need to check each database and make the change.
Although SQL Server has changed its naming convention to SQL Server 2000, 2005 and soon to be released 2008 the internal version numbers still remain.  Here is a list of the compatibly levels (versions) that you will see:

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005 
• 100 = SQL Server 2008 
• 110 = SQL Server 2012  

---

Identifying Compatibly Level
To check the compatibility level of your databases you can use one of these methods:
Using SQL Server Management Studio, right click on the database, select "Properties" and look at the "Options" page for each database as the following image shows:

Another option is to use sp_helpdb so you can get the information for all databases at once:
EXEC sp_helpdb

Or select directly from the sys.databases catalog to get the information for all databases.
SELECT * FROM sys.databases

---

Compatibly Level for New Databases
When issuing a CREATE DATABASE statement there is not a way to select which compatibility level you want to use.  The compatibility level that is used is the compatibility level of your model database.
Here is a sample CREATE DATABASE command, but there is not an option to change the compatibility level.

CREATE DATABASE [test] ON PRIMARY ( NAME = N'test', FILENAME = N'Z:\SQLData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'test_log', FILENAME = N'Y:\SQLData\test3_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%) GO

When creating a database using SQL Server Management Studio you have the ability to change the compatibility level on the "Options" tab such as follows:

If we use the "Script" option we can see that SQL Server issues the CREATE DATABASE statement and then issues "sp_dbcmptlevel" to set the database compatibility level to 80 as shown below.

CREATE DATABASE [test] ON PRIMARY ( NAME = N'test', FILENAME = N'Z:\SQLData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'test_log', FILENAME = N'Y:\SQLData\test3_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%) GO EXEC dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=80 GO

---

Changing Compatibility Level
So once you have identified the compatibility level of your database and know what you want to change it to you can use the sp_dbcmptlevel system stored procedure to make the change.  The command has the following syntax:

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ] --to change to level 80 dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=80 --to change to level 90 dbo.sp_dbcmptlevel @dbname=N'test', @new_cmptlevel=90 --or sp_dbcmptlevel 'test', '80' sp_dbcmptlevel 'test', '90'

---

Differences
There are several differences on how compatibly levels affect your database operations.  SQL Server Books Online has a list of these differences and the following list shows you a few of these items:

Compatibility level setting of 80 or earlier	Compatibility level setting of 90	Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional.	With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).	High
The *= and =* operators for outer join are supported with a warning message.	These operators are not supported; the OUTER JOIN keyword should be used.	High
SET XACT_ABORT OFF is allowed inside a trigger.	SET XACT_ABORT OFF is not allowed inside a trigger.	Medium

(Source: SQL Server 2005 Books Online)  For a complete list of these items look here:
In addition, each new compatibility level offers a new list of reserved keywords.  Here is a list of the new keywords for SQL Server 2005.

Compatibility level setting	Reserved keywords
90	PIVOT, UNPIVOT, REVERT, TABLESAMPLE
80	COLLATE, FUNCTION, OPENXML
70	BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP
65	AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK

(Source: SQL Server 2005 Books Online)
If one of these keywords is being used and your database is set to this compatibly level the commands will fail.  To get around this you could put the keyword in either square brackets ([ ]) or use quotation marks (" ") such as [PIVOT] or "PIVOT".

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.
   
   

   Compatibility for SQL Server 2005 (90), SQL Server 2008 (100), or SQL Server 2012 (110).