The answer lies in the execution plan:
Query #1 (ORDER BY
The SORT operator is seen before the Compute Scalar operator is seen. The sort on
item_id therefore happens first, and all that is left for the Compute Scalar operator to do is to concatenate the two strings (sql and item) based on the order referenced by the
Query #2 (ORDER BY newid()):
Here, the situation is reversed. Compute Scalar operation is performed before the SORT operation. The "newid()" returns a globally unique identifier and in this query is evaluated alongwith the concatenation operation. This value is also referenced by the sort operator, resulting in only one record being returned.
To cross-check, a simple modification can be made to the query:
select @sql=@sql+','+item from @t order by (SELECT newid())
Running the query now with the execution plan ON shows us that for all records/items in the
@t table, a Compute Scalar operation is executed which returns a newid() for each record. The SORT is now executed before the final Compute Scalar operation (which is the one actually concatenating the data) - because of which SQL Server concatenates data from all records.
commented on Nov 14 2012 1:12AM