Strange behavior of SQL Server - random selection

Question

I have been debugging the statement below for hours:

SELECT 
(
SELECT  t1.anotherColumn
FROM table1 t1
WHERE t1.aColumn=(1+ABS(Checksum(NewId()))%54)
) res, *
FROM 
(
SELECT TOP 200 * --PLEASE NOTICE HERE
FROM table2
)RESULT

and the problem is that always res contains the same value for each row. Now, if I change 200 that is highlighted in the query statement to any number below 176 it shows random rows of table1 which is the desired result!
Please note that the 54 has no effect on the results and it's there only because my table has 54 different values from 1 to 54 for aColumn.
I have tried this query on different tables and this strange behavior is repeated!

Answer 1

The difference in results may be explained by the differences in query plans.

SQL optimizer can choose to use Table Spool / Lazy Spool operator. In this case, NEWID() is called once, the GUID is stored in the temp table and used for all other rows.

https://technet.microsoft.com/en-us/library/ms191221(v=sql.105).aspx

... UPDATE: the query plan can be fixed by adding below line at bottom:

option(use plan 
N'
<your XML plan>
')

To catch "good" XML plan, run

SET SHOWPLAN_XML ON

and execute the query which shows expected result. Copy-paste it to OPTION(USE PLAN ...)

This solution works for me for the large number of rows (millions)

Answer 2

This will force the subquery to be reevaulated on every row and avoids the complicated random logic.

SELECT top 200
    (SELECT top 1 t1.anotherColumn from table1 t1 with( nolock ) where t2.t2Id is not null order by newid()  ) res, 
    *
FROM table2 t2

It works because newid() is already a random unique identifier and the comparison on table2 forces each row of table1 to be checked against the row in table2.