+= in SELECT clause; constant vs column [duplicate]

Question

I observed some strange behavior in MS SQL Server 2017.

• += in select acts as aggregator ('concatenate values from all rows') when on the right is a constant.
• += in select acts as 'just set the value' when on the right is a column name. (also, this turns of aggregating behavior for other columns)

So my questions are:

1. Why @c1 result contains value only last the row, even when += is used?
2. Why is @c2 affected, by the change +=->= for @c1?

Version 1:

BEGIN
    DECLARE
        @c1 NVARCHAR(MAX) = N'',
        @c2 NVARCHAR(MAX) = N'';

    SELECT
        @c1 = constraint_name, -- version-1
        @c2 += '+'
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    ORDER BY 1 DESC
    ;

    PRINT '@c1=' + @c1;
    PRINT '@c2=' + @c2;
END
;

Version 1 result:

@c1 = fk_abcde
@c2 = ++++++++++++++++++++++++++++++++++++++++++
(`@c2` result is aggregation of many rows; one plus for each row)

Version 2:

BEGIN
    DECLARE
        @c1 NVARCHAR(MAX) = N'',
        @c2 NVARCHAR(MAX) = N'';

    SELECT
        @c1 += constraint_name, -- version-2
        @c2 += '+'
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    ORDER BY 1 DESC
    ;

    PRINT '@c1=' + @c1;
    PRINT '@c2=' + @c2;
END
;

Version 2 Result:

@c1 = fk_abcde
@c2 = +
(`@c2` is just value assigned from last processed row)

This feels very strange - kind of like a bug. I fail to find any docs about this. The doc on '+= string' don't mention += usage in select query at all.

(at the moment my goal is to understand the behavior fully, so I would not accidentally step on it. Any hints to right documentation/keywords to search for would be helpful)

Answer 1

It's in the wrong place in the documentation, so not surprising that you haven't found it:

Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. Because, all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row

It's better to look for different ways of doing string concatenation. If your version supports it, choose to use STRING_AGG. For earlier versions, Aaron Bertrand has provided a good set of options (hat tip to Panagiotis Kanavos for providing the link)