Compatibility mode allows new command?

Question

I have a SQL Server 2017 server running a database in 2012 compatibility mode. However when I try using the new to 2017 T-SQL command string_agg(), it works. I would expect a syntax error because string_agg() isn't valid for 2012 mode.

Here is the SQL I'm using:

SELECT
    Compatibility_Level,
    CASE Compatibility_Level
       WHEN 65 THEN 'SQL Server 6.5'
       WHEN 70 THEN 'SQL Server 7.0'
       WHEN 80 THEN 'SQL Server 2000'
       WHEN 90 THEN 'SQL Server 2005'
       WHEN 100 THEN 'SQL Server 2008/R2'
       WHEN 110 THEN 'SQL Server 2012'
       WHEN 120 THEN 'SQL Server 2014'
       WHEN 130 THEN 'SQL Server 2016'
       WHEN 140 THEN 'SQL Server 2017 <<<'
       ELSE 'new unknown - ' + CONVERT(VARCHAR(10), Compatibility_Level)
    END AS VersionName
FROM 
    sys.databases
WHERE
    Name = DB_NAME()

DECLARE @x TABLE (col1 INT, col2 VARCHAR(5))

INSERT INTO @x 
VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2)

SELECT * FROM @x

SELECT col1, string_agg(col2,', ') 
FROM @x 
GROUP BY col1

Here is the output:

Compatibility_Level VersionName
------------------- ------------------------
110                 SQL Server 2012

(1 row affected)

(5 rows affected)

col1        col2
----------- -----
1           1
1           2
1           3
2           1
2           2

(5 rows affected)

col1        
----------- ------------------------------------
1           1, 2, 3
2           1, 2

(2 rows affected)

Answer 1

The purpose of Compatibility Mode is to ensure that statements which were valid in previous versions of SQL Server behave in the same way in newer versions of SQL server.

This includes statements which were previously valid but have become invalid in the new version, and statements which have a different meaning or behave differently between versions.

Statements which were invalid in previous versions are not included in this goal.

Why? Consider upgrading. You first want to convert everything to the new syntax and functions, then when you are satisfied everything is ready, you can raise the compatibility level. If you can't allow things which are valid in the new compatibility level (but invalid or meaningless before) then it becomes difficult to migrate to the higher level.