Click here to Skip to main content
Click here to Skip to main content

Tagged as

The SQL Server Rand Function

, 8 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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[^]

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pascal Ganaye
Software Developer (Senior)
United Kingdom United Kingdom
I am a French programmer.
These days I spend most of my time with the .NET framework, JavaScript and html.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 8 Feb 2012
Article Copyright 2012 by Pascal Ganaye
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid