Weird SQL Server behavior

Question

What´s the name of the behavior in the statement below?

Create table dbo.Foo(name varchar(10))

insert dbo.Foo (name)
select 'Weird'
union
select 'SQL'
union
select 'Server'
union
select 'behavior'

declare @title varchar(max)
set @title = ''
select @title = name + ' ' + @title from dbo.Foo

select @title

--returns 'Weird SQL Server behavior'

This can also be done with integers. I want to know the name for this behavior, that mix scalar and set operations.

Answer 1

String concatenation?

Answer 2

What exactly is weird about this? You select 4 rows, SQL Server runs:

 @title = name + ' ' + @title

Four times, and you end up with the string you mention.

Kinda lucky too, since the ordering is really random.

Answer 3

What´s the name of the behavior in the statement below?

I would call that a side effect from evaluating an expression for each row of a query.

Answer 4

This is one of the methods used sometimes to aggregate strings in SQL, see Concatenating Row Values in Transact-SQL. Is no recommended because it relies on order of execution. On a real table ou can get 'Weird SQL Behavior' but also 'Behavior Weird Server SQL'. The problem is documented in PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location.

Answer 5

Nobody mentioned that SELECT @title = name FROM dbo.Foo is the easier way to pick a value from a table and put it in a variable inside a SQL BATCH.

So, select @title = name + ' ' + @title from dbo.Foo picks the value of each row in dbo.Foo and add it in the end of @title.