Sql-server – Does assigning variable in SELECT do RBAR operations

sql servert-sql

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:

Result Set 1

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:

Results Image 2

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:

Results Image 3

Best Answer

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

Related Question