newid() returns one globally unique identifier and the result set is randomly sorted, also it return a single random row, so query2 is returning only one item in random way.

This issue (or behaviour) was discussed at sqlservercentral.com a few years ago as well as bug report was posted. Though MS answered that it was not a bug, this behavior was by design.

To answer your question:

Q1: Why is newid() not returning all the items in random order for concatenation and why does it choose only one randow item?

Using expressions in ORDER BY clause can result in undefined behavior, agree with @Olga.

Excerpt from MS KB article, link: http://support.microsoft.com/kb/287515

SQL Server query processor builds an different execution plan when expressions are applied to columns in the query's ORDER BY clause, than when those same expressions are applied to columns in the query's SELECT list. The decision made by the query processor is based on the cost of possible execution plans. The ANSI SQL-92 specification requires that any column referenced by an ORDER BY clause match the result set, defined by the columns present in the SELECT list. When an expression is applied to a member of an ORDER BY clause, that resulting column is not exposed in the SELECT list, resulting in undefined behavior. Additionally, the ANSI specifications regarding how an ORDER BY clause is to be applied state that the effect of the ORDER BY should be the same as if you take the entire result set produced by the SELECT list, and then perform the ordering of the table based on those columns in the SELECT list.

The answer lies in the execution plan: Query #1 (ORDER BY item_id): 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 item_id.

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.