T-SQL Query to find Supported Compatibility Level Values in SQL Server

The question came up on the forum - how do I find all the possible values for a specific compatibility_level in SQL Server?


According to the SQL Server version, the following T-SQL statement gives the compatibility level. It would be best to verify your SQL queries and their syntax for the supported compatibility levels.


use master;
set nocount on
 
Select
   'Server_name'       = @@servername
,   'Database_name'     = upper(sd.name)
,   'Supported Compatibility Level Values'   =
                case cast(sd.compatibility_level as varchar(255))
                                       
  when 80     then '80 (SQL 2000)     Works only on 80'
  when 90     then '90 (SQL 2005)     Works on 90, 80'
  when 100    then '100 (SQL 2008)    Works on 100, 90, 80'
  when 110    then '110 (SQL 2012)    Works on 110, 100, 90'
  when 120    then '120 (SQL 2014)    Works on 120, 110, 100'
  when 130    then '130 (SQL 2016)    Works on 130, 120, 110, 100'
  when 140    then '140 (SQL 2017)    Works on 140, 130, 120, 110, 100'
  when 150    then '150 (SQL 2019 or Azure SQL) Works on SQL 150, 140, 130, 120, 110, 100'
                                 
                end
from
   sys.databases sd
where
   database_id <> 2
order by
   sd.name
,   sd.compatibility_level desc

For demonstration, I executed the script on my Azure SQL Database and gets the following output.


Azure SQL Database Compatibilty Level

66 views0 comments