How to check the compatibility level of a database in SQL Server 2005

The compatibility level of SQL Server database defines the behaviors of that SQL database compared with compatible with earlier versions of SQL Server and defines the compability between the two SQL Server database versions.

In Microsoft SQL Server 2005 (aka Yukon) compatibility level can be set seperately for each database on an instance. This means a database on a MS SQL Server 2005 instance can have compability level equal to 90 (SQL Server 2005) and another database can have compability level of 80 (SQL Server 2000).

This means you can run your SQL 2000 database and application with a compability level of 80 on SQL Server 2005 to get benefit from new enhancements of the new sql version. And also you can alter your applications or create new applications based on SQL Server 2005 with compability level 90 and get use of the all new features introduced with the new SQL Server version, like CLR, new t-sql enhancements, etc.

You can check the compability of a database simply by running the below sql command.

sp_dbcmptlevel 'DatabaseName'

The below return messages are the samples of execution of sp_dbcmptlevel command on different databases.

The current compatibility level is 90.
or
The current compatibility level is 80.

Values of compability levels and their corresponding MS SQL Server versions are as follows:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005

An other method to determine the compability level of the installed database is by displaying the database properties in Microsoft SQL Server Management Studio.

If you open the database properties of the database and go to the Options page, you will see the Compability Level dropdown where you can view the current setting and also update to a new desired compability level.

There is also a way by using the t-sql codes to set the compability level of a SQL 2005 database. You can use the sp_dbcmptlevel to set the new compability level.

The syntax for using the sp_dbcmptlevel is shown below.

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]

For example, you can run the below command by replacing the DatabaseName to set its level of compability to 90 which means to SQL Server 2005.

sp_dbcmptlevel 'DatabaseName', 90

If you only run the command sp_dbcmptlevel, you will get a message of "Valid values of the database compatibility level are 60, 65, 70, 80, or 90."

If you run a successfull sp_dbcmptlevel command by specifying the @dbname and @new_cmptlevel parameter values, you will get the following message.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.