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
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
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?
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