STRING_AGG working on compatibility level < 140

Question

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?

Answer 1

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 -

• SQL Server 2017 (or higher)
• Azure SQL Database
• Azure Synapse Analytics (SQL DW)

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