How do you get the default compatibility level in Azure SQL Database from T-SQL?

Question

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.

Answer 1

There isn't a clean way that I know of. You could try set them all incrementally then see what the final value is. That will return the current maximum:

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'ALTER DATABASE '+DB_NAME()+' SET COMPATIBILITY_LEVEL = 130'; EXEC (@SQL)
SET @SQL = 'ALTER DATABASE '+DB_NAME()+' SET COMPATIBILITY_LEVEL = 140'; EXEC (@SQL)
SET @SQL = 'ALTER DATABASE '+DB_NAME()+' SET COMPATIBILITY_LEVEL = 150'; EXEC (@SQL)
SET @SQL = 'ALTER DATABASE '+DB_NAME()+' SET COMPATIBILITY_LEVEL = 160'; EXEC (@SQL)
SET @SQL = 'ALTER DATABASE '+DB_NAME()+' SET COMPATIBILITY_LEVEL = 170'; EXEC (@SQL)
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()
-- currently returns compatibility_level=150

Obviously you don't want to do this on an active application database as changing the levels could have consequences for the application users.

The test above uses ad-hoc SQL because otherwise the later versions fail syntax check without it even trying the rest, and you have to hard-code the database name. In SSMS or similar you could get around the first issue by breaking the batch into multiple:

ALTER DATABASE test_clvl SET COMPATIBILITY_LEVEL = 130
GO
ALTER DATABASE test_clvl SET COMPATIBILITY_LEVEL = 140
GO
ALTER DATABASE test_clvl SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE test_clvl SET COMPATIBILITY_LEVEL = 160
GO
ALTER DATABASE test_clvl SET COMPATIBILITY_LEVEL = 170
GO
SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()

If you can use outside tools, then script up a database create, then find the level from master.sys.databases, then drop the test DB, using powershell or similar.

In any case you'll need to ignore the error responses to the later versions. Perhaps TRY/CATCH can make this cleaner, but I'll leave you to play with that.

Answer 2

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.