sql-serverazure-sql-databasecompatibility-levelstring-agg

STRING_AGG working on compatibility level < 140


I understand STRING_AGG is introduced in SQL Server 2017 which is compatibility level 140.

The expected behaviour is to get error message 'string_agg' is not a recognized built-in function when run on 2016 or lower versions.

Got error when I run on on-prem SQL Server (2016). However, it is executing without error when I run on Azure SQL even after lowering compatibility level to 130 and even 110 did not give any error.

Is something changed in compatibility_level behaviour in Azure SQL? Like, both old and new syntax/code/features work?


Solution

  • This behavior is documented at the very last line of the remarks section:

    STRING_AGG is available in any compatibility level.

    Which means that if you are running an SQL Server version that supports string_agg -

    the string_agg built in function will work regardless of the compatibility level that set to the specific database you're working with.