Click here to Skip to main content
15,886,258 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 
Interesting question.
We're in hacking territory.
Perhaps if you get enough consecutive Rand() then you can use bruteforce and guess what seed is in.

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.