Databases
StackOverflow2013
Postsdbo
POT-SQL sum() of rand(checksum(newid()) is not random if appeared multiple times in the select list?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL sum() of rand(checksum(newid()) is not random if appeared multiple times in the select list?
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload