Share via

Default compatibility level 140 for Azure SQL databases

  • Article

As of this writing, the default compatibility level for new databases created in Azure SQL Database is 130. Very soon, we’ll be changing the Azure SQL Database default compatibility level for newly created databases to 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

For details on what compatibility level 140 specifically enables, please see the blog post: Public Preview of Compatibility Level 140 for Azure SQL Database.

Once this new database compatibility default goes into effect, if you still wish to use database compatibility level 130 (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 in Azure SQL Database use the same compatibility level as other databases in Azure SQL Database 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 (140).  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.

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 130 (or lower) instead of the new 140 default, execute ALTER DATABASE.  For example:

 ALTER DATABASE database_name  
SET COMPATIBILITY_LEVEL =  130;

Databases created prior to the new compatibility level default change will not be affected and will maintain their current compatibility level.  Also note that Azure SQL Database Point in Time Restore will preserve the compatibility level that was in effect when the full backup was performed.

Comments

  • Anonymous
    August 25, 2017
    This is an welcome change.
  • Anonymous
    September 03, 2017
    If I copy the database from 130 compatibility level, I assume the new database will also be off 130 compatibility level.Can you please confirm?
    • Anonymous
      September 06, 2017
      Hi Vijay,Confirmed. The copied database will use the compatibility level of the source.