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.

CREATE DATABASE CRM
	(
	  EDITION = 'BASIC',

	  MAXSIZE = 2GB
	)

SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'CRM'

130mode

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'

140mode
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.

 

One thought on “Azure SQL Database Compatibility Levels

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

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s