What the h*** is going on with the RAND() function? The way RNG's are supposed to work is you seed it first, then make subsequent calls to retrieve the subsequent numbers in the random sequence.
The RAND() function has an optional argument -- the seed. From this you might guess that you're supposed to do something like this to retrieve your random sequence:
RAND(23)
RAND()
RAND()
RAND()
RAND()
RAND()
What's confusing though is that the RAND() function always works even if you've never seeded it. I guess what it's doing is seeding itself using the system time or something. I say this because the essential problem that I have to fix is that I was using it this way, without seeding it, and noticed some non-random behavior when I executed RAND() at very regular intervals. What I was doing in a nutshell was that I had a loop in a C program that executes the stored procedure then sleeps 100 milliseconds, and it's the same stored procedure every time so it's likely taking about the same length of time to execute and get to the RAND() statement. That's a bit of a simplification but you get the idea.
So I want to get rid of this nonrandom behavior and stop it from seeding itself with the system time and instead use it the right way, where you seed it with some number and then retrieve successive numbers in the sequence. However, where I'm stuck is that if I call RAND() without arguments after seeding it myself, how do I know whether it's really getting successive numbers in the sequence or if it's seeding itself? Once you've seeded it to create a sequence, how long does that sequence persist? Does it go away at the end of the query, the end of the batch, when the connection is closed, or what?
My coworker and I wrote a RAND2 stored procedure based on an RNG algorithm he knew off the top of his head, but it's really damn slow for some reason, maybe because we had to write another SP to implement a floating point modulo function using an iterative algorithm. Basically I just need to know whether there's even any hope of using RAND() for this purpose or if I should give up and use RAND2().
The RAND() function has an optional argument -- the seed. From this you might guess that you're supposed to do something like this to retrieve your random sequence:
RAND(23)
RAND()
RAND()
RAND()
RAND()
RAND()
What's confusing though is that the RAND() function always works even if you've never seeded it. I guess what it's doing is seeding itself using the system time or something. I say this because the essential problem that I have to fix is that I was using it this way, without seeding it, and noticed some non-random behavior when I executed RAND() at very regular intervals. What I was doing in a nutshell was that I had a loop in a C program that executes the stored procedure then sleeps 100 milliseconds, and it's the same stored procedure every time so it's likely taking about the same length of time to execute and get to the RAND() statement. That's a bit of a simplification but you get the idea.
So I want to get rid of this nonrandom behavior and stop it from seeding itself with the system time and instead use it the right way, where you seed it with some number and then retrieve successive numbers in the sequence. However, where I'm stuck is that if I call RAND() without arguments after seeding it myself, how do I know whether it's really getting successive numbers in the sequence or if it's seeding itself? Once you've seeded it to create a sequence, how long does that sequence persist? Does it go away at the end of the query, the end of the batch, when the connection is closed, or what?
My coworker and I wrote a RAND2 stored procedure based on an RNG algorithm he knew off the top of his head, but it's really damn slow for some reason, maybe because we had to write another SP to implement a floating point modulo function using an iterative algorithm. Basically I just need to know whether there's even any hope of using RAND() for this purpose or if I should give up and use RAND2().