• <1 minute

Azure SQL Database query performance improvements for newly created databases

Improved query performance with compatibility level 130 in Azure SQL Database coming soon.

Azure SQL Database runs millions of databases under several different T-SQL versions, preserving backward compatibility for applications. SQL Database implements a versioning technique called “compatibility level.” The compatibility levels are:

  • SQL Server 2008 and SQL Database v11: 100
  • SQL Server 2012: 110
  • SQL Server 2014 and SQL Databasev12: 120
  • SQL Server 2016 and SQL Database v12 from mid-June: 130

Use of compatibility level 130 enables developers to benefit from new SQL Server 2016 query processor enhancements. These enhancements can improve your query executions by leveraging new features such as parallel plan execution, serial batch mode execution, and new query plan estimates, to name a few.

Starting in mid-June 2016, the default Azure SQL Database compatibility level will change from 120 to 130 for newly created databases. Existing databases created before mid-June 2016 will not be affected and will maintain their current compatibility level (100, 110, or 120).

For existing databases, we strongly encourage you to adopt this new compatibility level so you can benefit from the new query processor features and increase your applications performance. You can change the compatibility level by running a simple T-SQL statement: “ALTER DATABASE SET COMPATIBILITY_LEVEL = 130.”

Our experience shows that most workloads run as-is under compatibility level 130. However, there are always some exceptions, and due diligence is important to determine how much you can benefit from the enhancements. If you want to dive deeper, learn more here.