Using SqlServer 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.
Aucun commentaire:
Enregistrer un commentaire