We are announcing the official public preview of compatibility level 140 in Azure SQL Database.
Compatibility level 140 enables the following query optimizer changes:
- A trivial plan referencing Columnstore indexes will be discarded in favor of a plan that is eligible for batch mode execution.
- The sp_execute_external_script UDX operator is eligible for batch mode execution.
- Three adaptive query processing features are being introduced:
- Batch mode memory grant feedback, which improves the performance of repeating queries that request too much or too little memory.
- Batch mode adaptive join, which is a new query operator type that allows dynamic selection of the most optimal join algorithm based on runtime row counts.
- Interleaved execution, which improves the performance of queries that reference multi-statement table valued functions by using the true row count of the function call for use during query optimization.
Please note that this list is not exhaustive. Most optimizer hotfixes released after SQL Server 2016 RTM will be on by default in compatibility level 140.
The alignment of SQL versions to default compatibility levels are as follows:
- 100: in SQL Server 2008 and Azure SQL Database
- 110: in SQL Server 2012 and Azure SQL Database
- 120: in SQL Server 2014 and Azure SQL Database
- 130: in SQL Server 2016 and Azure SQL Database
- 140: in SQL Server 2017 and Azure SQL Database
To determine the current compatibility level of your database, execute the following Transact-SQL statement:
SELECT compatibility_level FROM [sys].[databases] WHERE [name] = 'Your Database Name';
Use of compatibility level 140 enables developers to benefit from query processor enhancements. To change the compatibility level of an existing database, execute ALTER DATABASE:
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 140;
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.
After SQL Server 2017 launches, the default Azure SQL Database compatibility level will change from 130 to 140 for newly created databases. Databases created before that time will not be affected and will maintain their current compatibility level. You can find more details at ALTER DATABASE Compatibility Level (Transact-SQL).