First published on MSDN on Jan 10, 2018
Database compatibility level 140 is now the default for new databases created in Azure SQL Database across almost all public regions. As of this writing, there are already
539,903 databases
in Azure SQL Database running in compatibility level 140.
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.
