title: T-SQL sum() of rand(checksum(newid()) is not random if appeared multiple times in the select list?
body: <p>Expression <code>rand(checksum(newid()))</code> is often used to generate random numbers.</p>
  
  <p>While generating some test data, I executed following statement:</p>
  
  <pre><code>select rand(checksum(newid())) R1, rand(checksum(newid())) R2
  from ftSequence(3)
  </code></pre>
  
  <p>where <code>ftSequence(N)</code> is a table function returning single column <code>N</code> and values <code>1, 2, 3 ... N</code> in its rows (as much as argument <code>N</code> is). Running this resulted to quite expected data:</p>
  
  <pre><code>R1                     R2
  ---------------------- ----------------------
  0,817                  0,9515
  0,3043                 0,3947
  0,5336                 0,7963
  </code></pre>
  
  <p>Then it was necessary to find sum in each column, and I did:</p>
  
  <pre><code>select sum(rand(checksum(newid()))) S1, sum(rand(checksum(newid()))) S2
  from ftSequence(3)
  </code></pre>
  
  <p>Surprisingly I got the same number in each column:</p>
  
  <pre><code>S1                     S2
  ---------------------- ----------------------
  1,2276                 1,2276
  </code></pre>
  
  <p>Why it happens?
  The same behavior for <code>avg</code>, <code>min</code> and <code>max</code> aggregate functions.
  Is it query optimizer, or do I miss some logic?</p>
  
  <hr>
  
  <p>More observations after comments.</p>
  
  <p>Placing <code>sum(rand(checksum(newid())))</code> into CTE or subquery like</p>
  
  <pre><code>select
      (select sum(rand(checksum(newid()))) from ftSequence(3)) S1,
      (select sum(rand(checksum(newid()))) from ftSequence(3)) S2
  </code></pre>
  
  <p>or</p>
  
  <pre><code>select sum(R1) S1, sum(R2) S2
  from (
      select rand(checksum(newid())) R1, rand(checksum(newid())) R2
      from ftSequence(3)
  ) R
  </code></pre>
  
  <p>as well as doing a trick like</p>
  
  <pre><code>select
      sum(rand(checksum(newid()))) S1
      , sum(rand(checksum(newid())) + 0) S2
  from ftSequence(3)
  </code></pre>
  
  <p>worked, resulting to different values</p>
  
  <pre><code>S1                     S2                    
  ---------------------- ----------------------
  0,7349                 1,478                 
  </code></pre>
  
  <p>Happy of that, and needed to produce more than one row of several different <code>avg(rand(checksum(newid()))) from ftSequence(3)</code>, I did following</p>
  
  <pre><code>select R.*
  from ftSequence(3) S1
      cross join (
          select
              avg(rand(checksum(newid()))) R1,
              avg(rand(checksum(newid())) + 0) R2
          from ftSequence(3)
      ) R
  </code></pre>
  
  <p>and got following result:</p>
  
  <pre><code>R1                     R2
  ---------------------- ----------------------
  0,6464                 0,4501
  0,6464                 0,4501
  0,6464                 0,4501
  </code></pre>
  
  <p>At this point I was not able to answer myself, whether is it correct result, or should values be all random? What are the ways to make all values random?</p>