65.9K
CodeProject is changing. Read more.
Home

The SQL Server Rand Function

starIconstarIconstarIconstarIconstarIcon

5.00/5 (2 votes)

Feb 1, 2012

CPOL
viewsIcon

22642

Reproducing the SQL RAND() function in C#

The documentation of T-SQL Rand() says: Repetitive calls of RAND() with the same seed value return the same results. This is really convenient, but what if you want to repeat the same series in C# or in any other language? The code below allows you to do just that. It took me quite a bit of time to get to this final version. I had to use various techniques to get it exactly right. I love the 12345 and 67890 at the end. When i got this bit, I knew it must be right.
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)
    {
        // this is the randomizer algorithm
        // it is used twice by IntRand
        // I haven't found the exact name of it.
        // I have called Shift and Carry because it was the nearest
        // described algorithm I could find.
        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
        
        int result = n1 - n2;
        if (result < 1) result += (2147483563 - 1); // same modulo than for n1
        return result;
    }
 
    #endregion "PRIVATE METHODS"
 
    public static double Rand()
    {
        const double RAND_DIVIDER = 2147483589.4672801884116202;
        return IntRand() / RAND_DIVIDER;
    }
 
    public static double Rand(int seed)
    {
        n1 = (seed < 0 ? -seed : seed == 0 ? 12345 : seed);
        n2 = 67890;
        return Rand();
    }
}
For info the algoritm uses, 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) I would quite like someone to go further and describe the algorithm. It must be described somewhere http://en.wikipedia.org/wiki/List_of_random_number_generators[^]