Random values in iterative cte's

Question

Using SQL Server 2016+

I have been having some difficulty in selecting random rows from a table which has been narrowed down to an issue with how random numbers are generated. For efficiency using a TOP 10 * ORDER BY NEWID() is to slow. In the code below I have used a set seed for repeatable results, but in live I would not.

Code sample:

SELECT  RAND(100) RN
,       RAND()
,       RAND()
,       RAND()
,       RAND();

SELECT  RAND(100) RN
UNION ALL SELECT  RAND()
UNION ALL SELECT  RAND()
UNION ALL SELECT  RAND()
UNION ALL SELECT  RAND();

WITH cte AS
    (SELECT 1         ID
     ,      RAND(100) RN
     UNION ALL
     SELECT cte.ID + 1
     ,      RAND()
       FROM cte
      WHERE ID < 5)
SELECT  RN
  FROM  cte;

Result Set

RN                                                                                          
---------------------- ---------------------- ---------------------- ---------------------- ----------------------
0.715436657367485      0.28463380767982       0.0131039082850364     0.28769876521071       0.100505471175005

(1 row affected)

RN
----------------------
0.715436657367485
0.28463380767982
0.0131039082850364
0.28769876521071
0.100505471175005

(5 rows affected)

RN
----------------------
0.715436657367485
0.28463380767982
0.28463380767982
0.28463380767982
0.28463380767982

(5 rows affected)

As you can see from the results when I call the RAND() function repeated in-line or via a union I get different results with each call. If however I call the function inside an iterative cte then I get the same value repeated.

This code is an example showing the problem, rather than the entire code set. I have created this purely as an example to demonstrate the problem. I have a solution based around a combination of Checksum & NewID() and the modulus calls and multiplication to give me values in the range I want, but this is fairly complex and seems excessive for just generating a list of random numbers in a range.

I'm looking for any guidance that can be offered on

Why this is happening
Any ways to solve the issue
Other options for generating lists of random numbers (That aren't RBAR)

Many thanks.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ae4008f4b71b97c432c5f527eca30670 - based on [How do I generate random number for each row in a TSQL Select?](https://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) — Lukasz Szozda, Jun 28 '19 at 09:52
A few other suggestions on https://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table — EzLo, Jun 28 '19 at 09:54
SQL Server sometimes calculates functions once only per SQL statement - for example you will see the same effect with GetDate(), which will not increment on any query however long it spends selecting records - as you have said, NewId() doesn't exhibit this, so it works for what you want. The rationale behind it is partly performance, I believe — Cato, Jun 28 '19 at 09:56
Thanks all. I had seen these type of solutions already. Order By NewID() becomes very inefficient on large tables, and my existing work around was based on the absolute checksum of newid mapped to a row. These are both good posts and I'm sure they will help others if they land here. — Matthew Baker, Jun 28 '19 at 09:59
>>Other options for generating lists of random numbers (That aren't RBAR) - download millions of digits of pi from somewhere and put it in a table with an id, then all you need to do is generate a random id as a seed then you can take the following digits of pi - is newid() inefficient in itself, or is it just the cost of the sort? — Cato, Jun 28 '19 at 10:01
Hi Cato. I understand what you've said, however I'm not sure how it applies in this case. I agree with calls like `getdate()` that doesn't increment with repeated calls, however `rand()` does generate multiple different values when used multiple times on the same line, or when manually linked via union. I'm unsure as to how embedding this inside a cte causes this. — Matthew Baker, Jun 28 '19 at 10:03
Hi Cato. The sorting is really inefficient when ordering by newid(). If using the absolute checksum newid() the repeated calculations start generating the expense. It is however more efficient than sort version when selecting low number of rows from high number of rows. There is a tipping point where the RBAR nature of iterative cte gets less and less efficient the larger the number of iterations. and then at a point the sorted version becomes faster. — Matthew Baker, Jun 28 '19 at 10:08
`TABLESAMPLE` is often overlooked, but it's more or less the most efficient way SQL Server can select "random" rows (on a sufficiently large table), sampling data pages as it does rather than rows. What it returns is not actually very random for individual rows (since, you know, page based) but you can use it to whittle down a larger set into something that `TOP(...) ... ORDER BY NEWID()` can operate on with reasonable efficiency. — Jeroen Mostert, Jun 28 '19 at 10:27
On the other hand, if you need true, statistically acceptable random numbers, neither `TABLESAMPLE` nor something based on a `RAND()` calculation will do; you can use `CRYPT_GEN_RANDOM()` to produce random bytes suitable for cryptographic purposes (which should also satisfy statisticians). Like `NEWID()`, it's calculated per invocation; unlike `NEWID()`, it doesn't have a few non-random bits in there and you can customize the amount of random bytes generated. For selecting rows it would be overkill, though, and `CONVERT(INT, CRYPT_GEN_RANDOM(4))` is not faster than `CHECKSUM(NEWID())`. — Jeroen Mostert, Jun 28 '19 at 11:16

score 1 · Accepted Answer · answered Jun 28 '19 at 10:53

RAND() returns a "constant" value within a query. That is, it is evaluated once per "mention" in the query.

You can see this if you run:

select rand(), rand()
from (values (1), (2), (3)) v(x);

Each row has two values, which are different. However, the columns have the same values.

In any case, the canonical answer is to use RAND(CHECKSUM(NEWID())). This provides a random seed for RAND() on each invocation:

WITH cte AS
    (SELECT 1 as ID, RAND(CHECKSUM(NEWID())) as RN
     UNION ALL
     SELECT cte.ID + 1, RAND(CHECKSUM(NEWID())) as RN
     FROM cte
     WHERE ID < 5
    )
SELECT RN
FROM cte;

This is probably good enough to get a list of random values. I suspect that your real problem is different -- something like extracting random rows from a table. If that is the real problem, then ask a new question or check similar questions.

Nice answer, and I appreciate the examples – Matthew Baker Jul 01 '19 at 08:05 — Matthew Baker, Jul 01 '19 at 08:05

Random values in iterative cte's

1 Answers1