1

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.

  • I don't know why you would want a TSQL Query to show you the maximum supported compatibility level. This page details them. docs.microsoft.com/en-us/sql/t-sql/statements/… – Jonathan Fite Nov 6 at 13:05
  • I've tried a few things, but at best they might tell you a new level is available - however that will happen while it is still in preview - not when it is the default.e.g 150 is available now (as I'm sure you know) but 140 is still the default. – Matthew McGiffen Nov 6 at 15:36
0

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.

0

Azure SQL Database runs the latest version of SQL Server under the hood. Do not get confused with the Azure Sql version which would be 'Microsoft SQL Azure (RTM) - 12.0.2000.8 '

So if you see we have SQL Server 2019 with compatibility 150 and should be available on your Azure database.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.