Monday 13 June 2016

What is Compatibility Level of DB in SQL Server?

The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server.
In a nutshell, this offers up partial "backward compatibility" to an earlier version.
This functionality isn’t all encompassing as only certain aspects (i.e. certain syntax) of the database would pertain to this setting.

List of compatibility levels in SQL Server:


SQL Server 6.0 – 60
SQL Server 6.5 – 65
SQL Server 7.0 – 70
SQL Server 2000 – 80
SQL Server 2005 – 90
SQL Server 2008 – 100
SQL Server 2012 – 110
SQL Server 2014 – 120
SQL Server 2016 – 130


Syntax to find compatibility level of database in SQL Server.



select name, CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
WHEN 110 THEN '110 (SQL Server 2012)'
WHEN 120 THEN '120 (SQL Server 2014)'
WHEN 130 THEN '130 (SQL Server 2016)'
END AS [compatibility level]
from sys.databases