Compatibility level

In the Ultra Transcenders books

DP-800

Each book explains Compatibility level in context, with comparison tables and the common traps.

Terms in this definition

• Schema

  The middle part of a Unity Catalog name (catalog.schema.table), grouping tables, views, volumes, functions and models inside a catalog. A grant on it covers everything in it now and later, and nothing inside can be reached without USE SCHEMA.

• T-SQL

  The dialect of SQL that Microsoft uses for Azure SQL and SQL Server. Azure Monitor logs are queried with KQL instead.

• LIKE

  Compares strings with a pattern that can contain the % and _ wildcards. Because it only understands character patterns, searching big volumes of text this way is much slower than using full-text search.

Related terms

• ALLOW_BUILTIN_TVF_IN_ALL_COMPAT_LEVELS

  Lets functions like REGEXP_SPLIT_TO_TABLE, REGEXP_MATCHES and OPENJSON be used whatever the database's compatibility level. It is a database scoped configuration, currently in preview, for SQL database in Fabric and Azure SQL Database.

• Intelligent query processing

  An umbrella name for optimiser capabilities, among them adaptive joins, OPPO, interleaved execution and feedback on memory grants, that improve query plans automatically, usually without code changes. Raising the compatibility level of a database switches most of them on.

• Interleaved execution

  An intelligent query processing feature, available from compatibility level 140, that stops optimising part-way, executes a qualifying multi-statement table-valued function to learn how many rows it really returns, and then completes the plan.

• OPPO

  With compatibility level 170, the optimiser keeps more than one cached plan per query, chosen by whether an optional parameter is NULL, so either a seek or a scan can be used as suits.

• REGEXP_LIKE

  Tests a string against a pattern and returns true or false; it can appear in WHERE clauses and CHECK constraints and requires compatibility level 170.

• REGEXP_MATCHES

  Returns a row for each place a pattern matches inside a string, giving the matching text, where it starts and any capture groups. Only available at compatibility level 170.

• REGEXP_SPLIT_TO_TABLE

  Breaks a string apart wherever a pattern occurs and returns every piece as a row, numbered by its ordinal. Compatibility level 170 is a prerequisite.

• Scalar UDF inlining

  From compatibility level 150, intelligent query processing can replace an eligible scalar user-defined function with an equivalent expression or subquery inside the query that calls it. The optimiser can then cost it properly and consider a parallel plan.