Compatibility level 130 for new Azure SQL databases
Microsoft announced new compatibility level 130 for new Azure SQL Databases on 10th May 2016.
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 Database: 120
-
SQL Server 2016 and SQL Database v12: 130
Use of compatibility level 130 enables developers to benefit from SQL Server 2016 query processor enhancements. The 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. Databases created before mid-June 2016 will not be affected and will maintain their current compatibility level (100, 110, or 120).
For pre-existing databases, Microsoft strongly encourage you to adopt this new compatibility level so you can benefit from the new query processor features and increase your application performance. You can change the compatibility level by running a simple T-SQL statement: “ALTER DATABASE <Your-Database-Name-Here> SET COMPATIBILITY_LEVEL = 130”.
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 on this matter, you can review the documentation: Improved query performance with compatibility level 130 in Azure SQL Database.
Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp
Follow @Sarab_SQLGeek