Click here to Skip to main content
15,879,184 members
Articles / Database Development / SQL Server
Tip/Trick

The SQL Server Rand Function

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
8 Feb 2012CPOL 22.2K   1   2
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.

C#
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)


Written By
Software Developer (Senior)
France France
I am a French programmer.
These days I spend most of my time with the .NET framework, JavaScript and html.

Comments and Discussions

 
QuestionWithout the seeds? Pin
barsham30-Jan-17 21:42
barsham30-Jan-17 21:42 
AnswerRe: Without the seeds? Pin
Pascal Ganaye19-Jun-17 3:43
Pascal Ganaye19-Jun-17 3:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.