This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
With last week’s launch of SQL Server 2019, database compatibility level 150 moves out of public preview and into general availability status. With this change in status, the default compatibility level for new databases created in Azure SQL Database is now 150.
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
- 150: in SQL Server 2019 and Azure SQL Database
For details on what compatibility level 150 specifically enables, see Intelligent query processing in SQL databases. The IQP feature family includes multiple features that improve the performance of existing workloads with minimal implementation effort.
Once this new database compatibility default goes into effect, if you wish to still use database compatibility level 140 or lower, please follow the instructions detailed to view or change the compatibility level of a database. For example, you may wish to ensure that new databases created in Azure SQL Database use the same compatibility level as other databases in Azure SQL Database. This is to ensure consistent query optimization behavior across development, QA, and production versions of your databases.
We recommend that database configuration scripts explicitly designate COMPATIBILITY_LEVEL rather than rely on the defaults, in order to ensure consistent application behavior.
For new databases supporting new applications, we recommend using the latest compatibility level, 150. 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 150 for SQL Server 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';
For newly created databases, if you wish to use database compatibility level 140 or lower instead of the new 150 default, execute ALTER DATABASE. For example:
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 140;
Databases created prior to the new compatibility level default change will not be affected and will maintain their current compatibility level. Also note that point in time backups will preserve the compatibility level that was in effect when the full backup was performed.