Does assigning variable in SELECT do RBAR operations? So, I discovered something interesting.  I was playing around with the TOP command and assigning variables in the select statement.  I issued the below query and found that the variable (@a) value was the last value returned from the Top Command.  It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop.

declare @a nvarchar(max)

select top 10 DisplayName
from dbo.Users

select top 10 @a = DisplayName
from dbo.Users

select @a as Results


Results:



This made me curious.  I modified the query to test again and executed the following:

declare @a nvarchar(max)
declare @b int = 0

select TOP 10 @a = DisplayName, @b = @b + 1
from dbo.Users

select @a as DisplayName, @b as Number


Results:



This is really interesting.  From these two queries, it almost seems like the TOP command is doing some sort of loop because of the cumulative sum that is happening on @b, as well as the assignments happening above.  Can someone explain what is going on?  Does SQL do a RBAR operation on when using the TOP command?

EDIT:

I also did this without the TOP commands, and see the results, so it's not just the TOP command.  It almost seems like assigning a variable in the SELECT statement does a RBAR command?

declare @a nvarchar(max)
declare @b int = 0

select @a = DisplayName, @b = @b + 1
from dbo.Users

select @a as DisplayName, @b as Number


Results:
It almost seems like for each value returned, that value is assigned to the variable, then each row in the result set is subsequently assigned, almost like a loop.


Yep.  That's what happens.  This is a side-effect of how variable assignment in SELECT queries were initially implemented.  But it was used enough that it can't really be changed, and even is documented:


  If a SELECT statement returns more than one row and the variable
  references a non-scalar expression, the variable is set to the value
  returned for the expression in the last row of the result set.


Variables (Transact-SQL)

and


  For backwards compatibility reasons, SQL Server provides support for
  assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most
  scope.


Ordering guarantees in SQL Server