The introduction of database compatibility level 170 is a significant update now applicable to Azure SQL Database and SQL Database in Microsoft Fabric. This level is crucial because compatibility levels align with specific SQL versions, influencing how queries are processed. As per Microsoft’s latest announcements, compatibility level 170 will be the default for new databases created without explicitly setting the compatibility level. This change embodies Microsoft’s ongoing efforts to improve performance with features like intelligent query processing, enabling better query execution with minimal implementation effort.
This adjustment won’t affect existing databases unless manually updated by the database owner, ensuring full control over compatibility upgrades. Therefore, it’s recommended for users to adopt the latest compatibility for optimal benefits while database configurations should specify COMPATIBILITY_LEVEL to maintain consistent application behavior across different server versions. To check your current database compatibility, querying the ‘compatibility_level’ in the sys.databases system catalog view is a handy method. Notably, restoring from a backup won’t change the compatibility level, preserving historical settings.
News: Database compatibility level 170 in Azure SQL Database and SQL database in Microsoft Fabric
Documentation: Intelligent query processing in SQL databases
