Database Compatibility Level 140 is now the default for new databases created in Azure SQL Database across almost all regions. At this point in time, there are already 539,903 databases in Azure SQL Database already running in Compatibility Level 140.
Frequently asked questions related to this announcement:
Why move to database Compatibility Level 140?
The biggest change is the enabling of the adaptive query processing feature family, but there are also query processing related fixes and batch mode improvements as well. For details on what Compatibility Level 140 specifically enables, see the blog post Public Preview of Compatibility Level 140 for Azure SQL Database.
What do you mean by “database Compatibility Level 140 is now the default”?
If you create a new database and don’t explicitly designate COMPATIBILITY_LEVEL, the database Compatibility Level 140 will be used.
Does Microsoft automatically update the database compatibility level for existing databases?
No, we do not update database compatibility level for existing databases. This is up to customers to do at their own discretion. With that said, we highly recommend customers plan on moving to the latest compatibility level in order to leverage the latest improvements.
My application isn’t certified for database Compatibility Level 140 yet. For this scenario, what should I do when I create new databases?
For this scenario, we recommend that database configuration scripts explicitly designate the application-supported COMPATIBILITY_LEVEL rather than rely on the default.
I created a logical server before 140 was the default database compatibility level. What impact does this have?
The master database of your logical server will reflect the database compatibility level that was the default at the time of the logical server creation. New databases created on a logical server with an older compatibility level for the master database will still use database Compatibility Level 140 if not explicitly designated. The master database compatibility cannot be changed without recreating the logical server. Having master at an older database compatibility level will not impact user database behavior.
I would like to change to the latest database compatibility level, any best practices for doing so?
For pre-existing databases running at lower compatibility levels, the recommended workflow for upgrading the query processor to a higher compatibility level is detailed in the article Change the Database Compatibility Mode and Use the Query Store. Note that this article refers to compatibility level 130 and SQL Server, but the same methodology applies for moves to 140 for SQL Server and Azure SQL DB.