Compatibility Level 140 has become a part of Azure default features and users can automatically get it once they create a database in Azure. The service is available in most regions and the number of databases running on Azure has reached up to 539, 903.
Exclusive Features: adaptive query processing is a major reason to opt for default compatibility level 140 which is an advanced query type that allows dynamic selection of join algorithms based on runtime row count. Batch mode memory grant feedback and interleaved execution are the additional features of Adaptive Query Processing.
Database Default Compatibility Level: the Compatibility level 140 will be used If COMPATIBILITY_LEVEL is not specified during the database creation.
Automatic Database Compatibility Level Update: Microsoft doesn’t update database automatically, so this choice is up to users to perform at their own discretion. However, customers are encouraged to move on to the advanced compatibility level to leverage the latest features. If your application isn’t certified for the database compatibility level 140 yet it is recommended that the configuration scripts explicitly designate the application-supported COMPATIBILITY_LEVEL rather than use the default.
Logical Server Created Prior To The Compatibility Level 140: the master database will have the compatibility level that was default at the time of the logical server creation. It is not possible to change the master database compatibility without recreating the logical server. Having master at an older database compatibility level will not impact user database behavior.
Best Practices To Change To The Latest Compatibility Level: see this article for the recommended workflow for upgrading the query processor to a higher compatibility level. The article refers to compatibility level 130 and SQL Server, but the same methodology applies for the upgrades to 140 for SQL Server and Azure SQL DB.
