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)
ALTER DATABASE AdventureWorks_name SET COMPATIBILITY_LEVEL = 90

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;
GO

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