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?
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.