Let’s start off with a quick overview of SQL Server versions and compatibility levels.

• 100 = SQL Server 2008 and Azure SQL Database
• 110 = SQL Server 2012 and Azure SQL Database
• 120 = SQL Server 2014 and Azure SQL Database
• 130 = SQL Server 2016 and Azure SQL Database
• 140 = SQL Server 2017 and Azure SQL Database

So with SQL Server 2017 now available to the public what level is a newly created Azure SQL Database set at?

I ran the following as a test.

CREATE DATABASE CRM
(
  EDITION = 'BASIC',
  MAXSIZE = 2GB
)
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'CRM'

To my surprise it is still at 130 level (Correct at the time of writing 3^rd November). I am not sure how long Microsoft will wait until 140 becomes the default but if you want to change it then it is very much possible.

ALTER DATABASE [CRM] SET compatibility_level  = 140
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'CRM'

Why would you want to move to the 140 level? Well if you want to leverage new features such as interleaved execution, batch mode memory grant feedback and adaptive joins then this is the way forward.

Filed under: Azure, Azure SQL DB Tagged: Azure, Azure SQL DB, SQL database, SQL server, TSQL