This has come up twice for me in the past few months so I figured it was worth a post.
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.
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:
To check what level a database instance is running at we have three different options.
The fastest (in my opinion) is to simply query the sys tables
SELECT compatibility_level
FROM sys.databases
WHERE name = 'some_database_name'
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
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.
Just like there are three ways of view a databases compatibility level, there are also three ways of changing it.
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
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
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.
For reference the best practice procedure for changing the compatibility level of database is in following these three steps.