SQL Server: Database compatibility, what it is exactly!

Database compatibility is the feature of database comes along with the SQL server version. Database supports the new features of the current version and the previous versions with the help of backward compatibility of SQL Server.

For example AlwaysOn, Replication and DB objects like DMVs etc.

 Different Compatibility levels:

Compatibility Level	Version
80	2000
90	2005
100	2008
110	2012
120	2014
130	2016
140	2017

Where to check it:

Right click database properties in object explorer and click options to see it.

When the Database Compatibility changes:

This value will change when 

a) we create the database from backup file.

b) Restoring the backup on existing DB.

c)  Upgrade the SQL Server.

How to change the DB Compatibility:

Using SSMS: object explorer->database->database properties->options->select the compatibility from drop down and click ok to save it.

T-SQL:

               ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = <Compatibility Level>

What forces you to change it: 

Changing the compatibility level is not a good practice though but when necessary we should do it (only 3 versions backward are supported though).

Legacy applications and desktop tools developed years ago and not upgraded would do it.

Did you see any other reason to lower the compatibility level?

Please review microsoft document for detailed info

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level#using-compatibility-level-for-backward-compatibility