How to change the Compatibility Level of a Database

Compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server. It affects behaviors only for the specified database, not for the entire server, and provides partial backward compatibility of the database with earlier versions of SQL Server.

SQL Server compatibility level values:

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

Compatibility level 60, 65, and 70 is no longer available in SQL Server 2008. Database containing an indexed view cannot be changed to a compatibility level lower than 80. When a database is set to backward-compatibility mode, some of the new functionalities may be lost.

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. That is why the bast practice for changing the compatibility mode is to set the database to single-user access mode first, change the compatibility level, and then to put the database back to multiuser access mode.

Transact-SQL:

ALTER DATABASE DBname SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

For example,

ALTER DATABASE DBname SET COMPATIBILITY_LEVEL = 90

sp_dbcmptlevel Stored Procedure:

EXEC sp_dbcmptlevel DBname, compatibility_level value;

For example,

EXEC sp_dbcmptlevel DBname, 90;

SQL Server Management Studio: