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)

- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

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)

Comment

Neat question! The follow up is "What difference would it make?"

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.

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(); } }

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.

**3** People are following this question.

Copyright 2019 Redgate Software.
Privacy Policy