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.

Microsoft SQL Server compatibility level values are actually the following:

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
105 = SQL Server 2008 R2
110 = SQL Server 2012
120 = SQL Server 2014
130 = SQL Server 2016

Compatibility levels 60, 65, and 70 is no longer available in SQL Server 2008 and above. 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 to change compatibility level:

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

For example:

ALTER DATABASE DBname SET COMPATIBILITY_LEVEL = 90

We could use, from SQL Server 2008, sp_dbcmptlevel, a system stored procedure:

EXEC sp_dbcmptlevel DBname, compatibility_level value;

For example:

EXEC sp_dbcmptlevel DBname, 90;

In SQL Server Management Studio we can change this value as follow:

Check the compatibility level of a database

Connect to the Database Engine, then, from the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute. This example returns the compatibility level of the AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
SELECT compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks2012';  
GO