What algorithm does SQL Server use to generate random numbers for the RAND() function? I believe it is either a linear congruential generator or a mersenne twister, but cannot find any information stating which (or if it is something else entirely)
asked
What algorithm does RAND() in SQL Server use?
According to the "Using RAND" Books Online entry: *"The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function"* The (non-mersenne!) twist there is that Microsoft don't provide source code for their implementation of the stdlib rand function. The answer provided by @paganaye looks very plausible. Might be a
http://en.wikipedia.org/wiki/Multiply-with-carry implementation?
I wouldn't use RAND. Nowadays I use ABS(CHECKSUM(NEWID())) % 1000 to get random number between 0 and 999. It has better distribution than RAND(). Also RAND() alwasys returns same value first time after SQL Server restart, if I remember correctly.
From BOL: Repetitive calls of RAND() with the same seed value return the same results. For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.
I have test your statement "Also RAND() alwasys returns same value first time after SQL Server restart, if I remember correctly." and I believe it is not true.
Here is my attempt to guessing the Algorithm used in SQL Rand() This algorithm returns the same value than SQL on my machine. Let me know if this works with you. This is C# code. public class SQLRand { private static int n1; private static int n2; #region "PRIVATE METHODS" private static void ShiftAndCarry(ref int n, int LEQA, Int64 MULTIPLIER, int SHIFT, int MODULUS) { n = n * LEQA - ((int)(n * MULTIPLIER >> SHIFT)) * MODULUS; if (n < 0) n += MODULUS; } private static int IntRand() { ShiftAndCarry(ref n1, LEQA: 40014, MULTIPLIER: 327796565, SHIFT: 44, MODULUS: 2147483563); // The 105,097,561st prime ShiftAndCarry(ref n2, LEQA: 40692, MULTIPLIER: 1333397965, SHIFT: 46, MODULUS: 2147483399); // the 105,097,554th prime // We used two of the bigger prime that would fit a C# integer. // The biggest prime number that would fit is int.MaxValue = 2^31 - 1 = 2,147,483,647 (the 105,097,565th prime) int result = n1 - n2; if (result < 1) result += (2147483563 - 1); // same modulo than for n1 return result; } #endregion "PRIVATE METHODS" /// /// Returns a pseudo-random number. /// /// double value from 0 through 1, exclusive public static double Rand() { const double RAND_DIVIDER = 2147483589.4672801884116202; return IntRand() / RAND_DIVIDER; } /// /// Initiate the pseudo-random number generator using a seed, and return a 'not-so-random' first value. /// ///
Seed is an integer that fixes the returned value and subsquent calls. /// double value from 0 through 1, exclusive public static double Rand(int seed) { n1 = (seed < 0 ? -seed : seed == 0 ? 12345 : seed); n2 = 67890; return Rand(); } }
@paganaye How can this line possibly work?
const double RAND_DIVIDER = 2147483589.4672801884116202;Double numbers are approximate and cannot possibly have more than 15 (sometimes 16) accurate digits, so
double a = 2147483589.4672801884116202;
double b = 2147483589.46728017;
double c = 2147483589.46728013;
Console.WriteLine("a equal to b: " + (a == b).ToString());
Console.WriteLine("b equal to c: " + (b == c).ToString());
Console.WriteLine("a equal to c: " + (a == c).ToString());
Console.ReadLine(); The above produces true for all of them.
Your algorithm works for seeded numbers but it does not work for Rand() call without any seed. Any idea?
3 People are following this question.
