what is database compatibility mode and how to set it.?

Problem: what is database compatibility mode and how to set it.?

Solution:  ALTER DATABASE Compatibility Level

Compatibility is a setting which allows to support the backward compatibility in a language level or syntax level. Sets certain database behaviors to be compatible with the specified version of SQL Server.

Setting the compatibility is a must if features are getting deprecated in between the versions. like Merge syntax wont work in SQL 2005 but works in SQL 2008 or higher. So think of any new functionalities which gets skipped due to this compatibility issues.

You can see from the SQL server management studio and see the options you have when you click on the properties of a Database.  Each version of SQL Server introduces a new compatibility level and supports two previous levels for backward compatibility.

If you can see the below picture is pulled up from SQL Server 2014 management studio and the compatibility supported are shown in the drop down.

Compatibility will be set by the database when you create a database from a version of SQL Server, depending on what version the database is created the compatibility is designated based on that version.

Compatibility Can be Altered using Alter Database or sp_dbcmptlevel .

It is best practice to move the DB into single-user mode before changing the compatibility

Syntax:

ALTER DATABASE database_name   

SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 }  

sp_dbcmptlevel [ [ @dbname = ] name ] 

    [ , [ @new_cmptlevel = ] version ]

For example, if I create a database in SQL Server 2012, the database gets the compatibility 110 as it is designated for 110. Please see the below matrix for compatibility levels designations and supported compatibilities.

Product	Database Engine Version	Compatibility Level Designation	Supported Compatibility Level Values
SQL Server vNext	14	140	140, 130, 120, 110, 100
SQL Server 2016	13	130	130, 120, 110, 100
SQL Database	12	120	130, 120, 110, 100
SQL Server 2014	12	120	120, 110, 100
SQL Server 2012	11	110	110, 100, 90
SQL Server 2008 R2	10.5	105	100, 90, 80
SQL Server 2008	10	100	100, 90, 80
SQL Server 2005	9	90	90, 80
SQL Server 2000	8	80	80

Why do the compatibility matter?

Because SQL server introduced features or keywords in each release version of the product. Below are the keywords introduced in each version of SQL Server.

Compatibility-level setting	Reserved keywords
130	To be determined.
120	None.
110	WITHIN GROUP, TRY_CONVERT, SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYDETAILSTABLE, SEMANTICSIMILARITYTABLE
100	CUBE, MERGE, ROLLUP
90	EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE

Example:

Example 1: In this example, I would like to show we can Raise the compatibility of a Database to the product designated compatibility.

-- setting the database to single user mode before changing the compatibility.

ALTER DATABASE DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- method 1

ALTER DATABASE  DB SET COMPATIBILITY_LEVEL = 120

--method 2

EXECUTE sp_dbcmptlevel 'DB',   120

--to view output after the changes.

SELECT NAME,   compatibility_level  FROM sys.databases WHERE NAME =              'db'

OUTPUT:

NAME	compatibility_level
DB	120

Example 2:

In this example, I would like to show how we can know what all databases have compatibility less than the product specified compatibility.

DECLARE @ver TINYINT;

SET @ver = PARSENAME(CONVERT(SYSNAME,SERVERPROPERTY(N'ProductVersion')),4) + '0';

 Select @ver as product_compatibility

SELECT name, [compatibility_level]

  FROM sys.databases

  WHERE [compatibility_level] < @ver

  ORDER BY 1,[compatibility_level];

 OUTPUT: