Surprising behavior of re-assigning a variable for every row in a select

Question

This is the simplest example with which I could reproduce the issue. As such it looks a little bit contrived, but bear with me.

declare @t table(number int)
insert into @t values (1), (2)

declare @sum bigint = 0

select @sum = @sum + number
    from (select top 2 number from @t order by number) subquery
    order by number desc

select @sum

Here's the query on the data explorer.

I would expect this to return 3, the sum of the values in the table @t. Instead, however, it returns 1.

Doing any of the following will cause the query to correctly return 3:

• make @t.number and @sum have the same type (by making @sum an int or @t.number a bigint).
• removing the outer order by
• removing the inner order by
• making both order bys sort in the same direction by adding desc to the inner one or removing it from the outer one
• removing the subquery (i.e. just selecting from @t)

None of these things strike me as something that should change the behavior of this query.

Swapping the sort orders (descending in the subquery, ascending on the outside) will make the query return 2 instead of 1.

A similar thing happens with strings instead of numbers, so this isn't constrained to int and bigint.

This happens both with SQL Server 2014 and 2016, or to be precise

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
Feb 20 2014 20:04:26 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )

and

Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64)
Jul 11 2016 22:05:22
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

(the latter being the data explorer).

What's going on?

Answer 1

The answer seems to be that you are/were relying on undocumented behaviour which changed in Sql Server 2012.

Per the documentation:

• https://msdn.microsoft.com/en-us/library/ms187330.aspx

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

It is not documented what happens if the destination variable (to be assigned to) is part of the source expression. It seems this behaviour has changed. In earlier versions the variable would be assigned once for each row, but that doesn't seem to occur any more.

This is most visible for a lot of functions where the "group concat" trick ceased to work:

SELECT @sentence = @sentence + ' ' + word from  SENTENCE_WORDS order by position

These have generally to be replaced by the xml concat trick.

set @sentence = (
    select word as "text()", ' ' as "text()" 
    from SENTENCE_WORDS 
    order by position 
    for xml path(''), root('root'), type
).value('(/root)[1]', 'nvarchar(max)')