T
theSQLSith
Database compatibility level 160 is now the default for new databases created in Azure SQL Database across almost all public regions.
The alignment of SQL versions to default compatibility levels are as follows:
For details about which feature, or features compatibility level 160 enables, please see Intelligent query processing in SQL databases. The IQP family of features includes multiple features that improve the performance of existing workloads with minimal or no implementation effort.
Once this new database compatibility default goes into effect, if you still wish to use database compatibility level 150 (or lower), please follow the instructions detailed here: View or Change the Compatibility Level of a Database . For example, you may wish to ensure that new databases created on the same logical server use the same compatibility level as other Azure SQL Databases to ensure consistent query optimization and execution behavior across development, QA and production versions of your databases. With this example in mind, we recommend that any database configuration scripts in use explicitly designate the 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, 160. 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 database compatibility level 130 and SQL Server, but the same methodology that is described applies to database compatibility 160 for SQL Server and Azure SQL Database.
To determine the current database compatibility level, query the compatibility_level column of sys.databases system catalog view.
Ok, we believe that there may be a few questions that we have not directly answered with this announcement. Maybe questions such as:
What do you mean by “database compatibility level 160 is now the default”?
If you create a new database and don’t explicitly designate COMPATIBILITY_LEVEL, the database compatibility level 150 will be used.
Does Microsoft automatically update the database compatibility level for existing databases?
No. We do not update the database compatibility level for existing databases. This is up to you as an owner of your database to do at your own discretion. With that said, we highly recommend that you plan on moving to the latest database compatibility level in order to leverage the latest improvements that are enabled with the latest compatibility level.
I created a logical server before 150 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 when the logical server was created. New databases created on this logical server with an older compatibility level for the master database will use database compatibility level 160 if the database compatibility level is not explicitly specified. 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.
Would the database compatibility level change to 160 if I restore a database from a point in time backup before the default changed?
No. We will preserve the compatibility level that was in effect when the backup was performed.
Continue reading...
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
- 160: in SQL Server 2022 and Azure SQL Database
For details about which feature, or features compatibility level 160 enables, please see Intelligent query processing in SQL databases. The IQP family of features includes multiple features that improve the performance of existing workloads with minimal or no implementation effort.
Once this new database compatibility default goes into effect, if you still wish to use database compatibility level 150 (or lower), please follow the instructions detailed here: View or Change the Compatibility Level of a Database . For example, you may wish to ensure that new databases created on the same logical server use the same compatibility level as other Azure SQL Databases to ensure consistent query optimization and execution behavior across development, QA and production versions of your databases. With this example in mind, we recommend that any database configuration scripts in use explicitly designate the 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, 160. 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 database compatibility level 130 and SQL Server, but the same methodology that is described applies to database compatibility 160 for SQL Server and Azure SQL Database.
To determine the current database compatibility level, query the compatibility_level column of sys.databases system catalog view.
SELECT name, compatibility_level FROM sys.databases;Ok, we believe that there may be a few questions that we have not directly answered with this announcement. Maybe questions such as:
What do you mean by “database compatibility level 160 is now the default”?
If you create a new database and don’t explicitly designate COMPATIBILITY_LEVEL, the database compatibility level 150 will be used.
Does Microsoft automatically update the database compatibility level for existing databases?
No. We do not update the database compatibility level for existing databases. This is up to you as an owner of your database to do at your own discretion. With that said, we highly recommend that you plan on moving to the latest database compatibility level in order to leverage the latest improvements that are enabled with the latest compatibility level.
I created a logical server before 150 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 when the logical server was created. New databases created on this logical server with an older compatibility level for the master database will use database compatibility level 160 if the database compatibility level is not explicitly specified. 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.
Would the database compatibility level change to 160 if I restore a database from a point in time backup before the default changed?
No. We will preserve the compatibility level that was in effect when the backup was performed.
Continue reading...