Changing The Compatibility Level Of A SQL Server Database

Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

This has come up twice for me in the past few months so I figured it was worth a post.

Upgrading Your Database Server Does Not Automatically Upgrade Your Databases

Whenever you move to a newer version of SQL Server or upgrade an existing server to a newer version using either the backup & restore method or detach & attach method, the compatibility level of the individual databases on it do not automatically get upgraded as well. This means your databases will still act as though they are running on an earlier version of SQL Server.

This is actually intentional as occasionally features and behaviors will change between versions so its better to give the dba the final decision to upgrade a level or not. In reality this is not a major problem, but in the long run you will not be able to take advantage of newer features unless you upgrade your databases compatibly level to the current version.

The Different Compatibility Levels

To get started lets look at the compatibility levels for different SQL Server versions. The compatibility level is a simple integer corresponding to a particular version name. As of today these are:

  • 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 & 2008 R2
  • 110 = SQL Server 2012

Get A Databases Current Compatibility Level

To check what level a database instance is running at we have three different options.

Query System Objects

The fastest (in my opinion) is to simply query the sys tables

SELECT compatibility_level 
FROM sys.databases
WHERE name = 'some_database_name'

Use "sp_helpdb"

Another way is to run the "sp_helpdb" command and look at the "compatibility_level" column for the database you are interested in.

EXEC sp_helpdb

View Properties In Management Studio

Finally if you prefer to use SQL Server Management Studio, right click on the database in the object explorer tree and select "Properties". The databases compatibility level can be found on the "Options" page.

Change Compatibility Level

Just like there are three ways of view a databases compatibility level, there are also three ways of changing it.

With T-SQL

My preferred method is to simply use an alter command like this.

-- Set AdventureWorks database compatibility level to 90 (2005)

With "sp_dbcmptlevel"

The system stored procedure "sp_dbcmptlevel" sets certain database behaviors to be compatible with the specified version of SQL Server.

-- Set AdventureWorks database compatibility level to 90 (2005)
EXEC sp_dbcmptlevel AdventureWorks, 90;

Change In Management Studio

Finally if you are using SQLServer Management Studio to view the compatibility level, you can simply change the level from the same screen by selecting the drop down.

Best Practice

For reference the best practice procedure for changing the compatibility level of database is in following these three steps.

  1. Set the database to single user access mode by using ALTER DATABASE SET SINGLE_USER
  2. Change the compatibility level of the database.
  3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER

Reader Comments

subash's Gravatar
Wednesday, March 11, 2015 at 12:10:04 PM Coordinated Universal Time

We just had a peoplesoft upgrade from 9.0 to 9.2. As part of the process the database was upgraded to SQL Server 2012 from SQL Server 2005. However, the compatibility level of the database wasn't changed. We had done UAT testing for peoplesoft upgrade with database compatibility level 90.

Are there any negative effects to change the compatibility level to 110 (from 90) now?

Steven Neiland's Gravatar
Steven Neiland
Tuesday, March 17, 2015 at 2:25:12 PM Coordinated Universal Time

Subash. You need to do your uat at level 110 to know for certain. I can't imagine any scenario where there would be an issue but I dont know how your database is put together.

Brutus's Gravatar
Monday, August 17, 2015 at 8:10:43 AM Coordinated Universal Time

I have live databases running on SQL 2005 at compatibility level 90 , and I want to move them to 2014 on a new VM? compatibility level 90 can't even be loaded in to 2014, do I need a 2012 in-between server?

Steven Neiland's Gravatar
Steven Neiland
Wednesday, September 2, 2015 at 1:24:03 PM Coordinated Universal Time

The upgrade process should change the compatibility levels for you. What I suggest you do is setup a second 2005 server and copy the database to that. Then upgrade that database server to 2014. That will run a series of update scripts to your database to make it compatible.

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing