How to get the default compatibility level in Azure SQL Database from T-SQL

azureazure-sql-databasecompatibility-level

In on-prem SQL Server you can check the server version and multiply by 10 to get the maximum compatibility level supported by the server, or check the compatibility level of the model database to get the default compatibility level new databases will be created with.

In Azure SQL Database, neither of these work; the server version stays at 12 even though the maximum compatibility level is 140, and there isn't a model database to check against.

How do you find out what the current default compatibility level is in Azure SQL Database, from T-SQL? In other words, I want a T-SQL query that would have returned 130 if run before 18th Jan 2018, returns 140 if run today, and will return 150 whenever Azure updates to a default compatibility level of 150.

Best Answer

I couldn't find anything specifically documented for this, but:

SELECT SERVERPROPERTY('ResourceVersion');

...currently returns "15.00.2000" for Azure SQL Database, which may be useful to you.

I had thought you might be able to query:

SELECT H.[name]
FROM sys.dm_exec_valid_use_hints AS H 
WHERE H.name LIKE N'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_%';

...but that returns:

name
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160

...which are all valid hints, but 150 is the maximum you can set database compatibility to at the time of writing.

Related Question