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