Random Number Recalculation Bug

The problem in outline

A simple expression can assign a random order or row number to a set of records.  The following example assigns a random decimal value between 0 and 1 to each row in a set:

CAST(RANK() OVER (ORDER BY NEWID()) / @TotalRows  AS decimal(5,3)) AS RandomPercentile

This works well until the randomised set is joined with another table.    Depending on the query plan that SQL server chooses, it is quite possible for the randomised set to be evaluated several passes.    Were RANK() ordered by another value in the same table the value would not change.  But using a random order directive means the order will be different on each evaluation.  The output will be a subset of the desired rows, with some rows appearing more than once and with a different RANK (or, in the above example, different RandomPercentile) each time.


1. Persist the randomised value to a temporary table before joining it.

2. Explore the tempting alternatives in Solution to Catch That Bug! Random Row per Group  in SQL Server Pro.  (putting the Random generation in a CROSS-APPLIED derived table).