Compatibility Level

Compatibility Level

RTM = Release to manufacturing, SPn = Service Pack n, CTP = Community Technology Preview (beta release).

SQL Server Version	Internal Database Version	Database Compatibility Level	Supported Database Compatibility Levels
SQL Server 2019 CTP 3.2 / RC 1 / RC 1.1 / RTM	904	150	150,140,130,120,110,100
SQL Server 2019 CTP 3.0 / 3.1	902	150	150,140,130,120,110,100
SQL Server 2019 CTP 2.3 / 2.4 / 2.5	897	150	150,140,130,120,110,100
SQL Server 2019 CTP 2.1 / 2.2	896	150	150,140,130,120,110,100
SQL Server 2019 CTP 2.0	895	150	150,140,130,120,110,100
SQL Server 2017	868 / 869	140	140,130,120,110,100
SQL Server 2016	852	130	130,120,110,100
SQL Server 2014	782	120	120,110,100
SQL Server 2012	706	110	110,100,90
SQL Server 2012 CTP1 (a.k.a. SQL Server 2011 Denali)	684	110	110,100,90
SQL Server 2008 R2	660 / 661	100	100,90,80
SQL Server 2008	655	100	100,90,80
SQL Server 2005 SP2+ with VarDecimal enabled	612	90	90,80,70
SQL Server 2005	611	90	90,80,70
SQL Server 2000	539	80	80,70
SQL Server 7.0	515	70	70
SQL Server 6.5	408	65	65
SQL Server 6.0	?	60	6

 Note: Collected data from sqlserverbuilds.blogspot.com

FAQ:- My client was working with SQL Server 2005. I have attached the db into SQL Server 2008. How can you differentiate?

* We can differentiate using one db property "Compatibility Level".

           SQL Server Version   Compatibility Level

          ---------------------    ---------------------

                   2000             80

                   2005             90

                   2008             100

                   2012             110

                   2014             120

                   2016             130

                   2017             140

                   2019             150

FAQ: - I have copied/restored SQL Server 2005 database into SQL Server 2008. How can you identity it?

Ans:    By using compatibility level we can differentiate the databases.       

·         SS is RDBMS product developed by MS.

·         It supports - SQL

ü  It is common db language developed by IBM

ü  Non procedural lang

ü  Case insensitive lang

 More information changes on the respective versions click here

USE AdventureWorks2012;

GO

SELECT compatibility_level

FROM sys.databases WHERE name = 'AdventureWorks2012';

GO

ALTER DATABASE AdventureWorks2012

SET COMPATIBILITY_LEVEL = 120;

GO