Azure SQL Database Compatibility Levels

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.



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 3rd 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.



2 thoughts on “Azure SQL Database Compatibility Levels

  1. Pingback: Dew Drop - November 7, 2017 (#2598) - Morning Dew

  2. Pingback: Azure SQL Database Compatibility Levels - SSWUG.ORG

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s