I have to generate random number with a given seed value.

I am able to do that, except that the values genered in SQL Server is
not matching the same random values generation in VB with same seed
value.

In VB I am doing the following
Randomize(40)
calling Rnd function multiple times.

In SQL Server I am doing the following
DECLARE @i AS INT
DECLARE @SeedValue as INT
SET @SeedValue = 40
set nocount on
SELECT RandomSeed = RAND(@SeedValue)
SET @i = 1
WHILE @i <= 10
BEGIN
    SELECT SequenceID = @i, RandomNumber = RAND()
    SET @i = @i + 1
END

Each application generates same set of random numbers, but the random
numbers between both applications do not match.

With seed value 40,

VB values are like this
0.642230093479156
0.204699516296387
0.996722400188446
0.119860291481018
0.440326988697052
0.8467857837677
0.211332738399506
0.303421854972839

SQL Server values are like this
0.54985053752746782
0.39546282223775731
0.99748885882354155
0.64167996428873098
0.84922162057238226
3.2527740068704403E-2
0.60898295959710558
0.58786151856609326

Is this by design? Is it possible to get the matching values between
two application with the same seed value.

Any information would be appreciated.

Thanks
SAI

Is there supposed to be something wrong with those numbers? They're
completely random. :)

If you're worried about the fact that the numbers returned by SQL have a
different scale than the ones returned by VB, then simply cast one or the
other into a compatible scale and/or precision. In SQL you can actually
design your own function to mimic the one used in VB.

But the main question remains - why?


ML

Duh!

Is this a joke?  or do you really have no idea what "random number
generator" means? (Google it!!)  Obviously How much math did you have
in college, if ANY?

Well punk, you are supposed to get the same number after calling the
randomize with same seed value. Run this SQL in TSQL multiple times and
you see the same values are generated.

In SQL Server I am doing the following
DECLARE @i AS INT
DECLARE @SeedValue as INT
SET @SeedValue = 40
set nocount on
SELECT RandomSeed = RAND(@SeedValue)
SET @i = 1
WHILE @i <= 10
BEGIN
        SELECT SequenceID = @i, RandomNumber = RAND()
        SET @i = @i + 1
END

Sure, but this is inside the same application, so it's using the same
algorithm and seed to generate the values.
I don't know much about Random Number Generators but maybe not all
applications use the same algorithm to generate them or this could be a
precision/scale problem as ML suggested.
If you absolutely need that the 2 produce the same results, and it isn't a
precision/scale problem, you will have to use the same generator to produce
them in SQL and VB.
Maybe you can Google and find the code for a generator that can be adapted
to SQL and VB.
As for Joe Celko (alias Punk), you haven't been in this newsgroup for very
long, have you?
We've all learned (at least most of us) to take the good and ignore the bad.

Show quoteHide quote

Thanks for the replies and pretty good answers about swapping numbers.

Coming back to original issue, it looks like VB and SQL are using
different algorithms to generate random numbers, atleast based on the
results, although I am not an expert in random number generation. Here
is the exactly problem I am trying to resolve, may be you guys can give
me better idea.

I am doing encryption of certain values in VB using seed and a key. I
am using seed to generate random numbers and doing whole bunch of
mathetical operations. The encrypted values are stored in DB, and I
want to retrieve the encrypted values from DB and decrypt them in my
application. This works fine, but I am trying to implement Decrypt
algorithm in SQL server.  The encryption and decryption are done in VB
COM object, one of my programmers wrote some applications which will
retrieve the decrypted values straight from SQL Server by using our
encryption.dll with sp_OACreate and sp_OAMethod, it works beautiful,
but the problem is its timing out as the rows were increased
significantly.

I am trying to resolve this by implementing the decryption algorithm in
SQL server rather than using VB COM dll. At the same time I am
modifying the applications to do the decryption in application rather
than executing in SQL Server.

Its not a big deal if I cant implement decryption algorithm in SQL
Server, but I was curious to know the random number generation between
the application.

Thanks for your responses.

SAI

Regarding my Math, lets see how much you have.

Swap two numbers without using temp variables.

On 14 Nov 2005 07:01:19 -0800, Sai wrote:

Assuming "number" means "integer":

A := A XOR B
B := A XOR B
A := A XOR B

If "number" means something else, like floating point, then I suppose you
could always cast it to a series of bytes (or words of whatever length you
can handle) and do it piecemeal.

I haven't done math problems in a while:
A = A + B
B = A - B
A = A - B

Show quoteHide quote

On Tue, 15 Nov 2005 09:24:56 -0500, Raymond D'Anjou wrote:

Hey, why didn't I think of that?  works for any real numbers, not just
integers like my XOR.

You know, I think it works with any operators that are inverses.

A = A * B
B = A / B
A = A / B
(of course neither can be zero here.)

XOR is special because it is its own inverse.

Of course now I cannot think of another pair of operators that works...

well, this works, but it's prone to inaccuracy, and has an even more
restricted domain (positive reals):

A = A ^ B
B = A ^ (1/B)
A = ln(A) / ln(B)

Show quote Hide quote It just jumped into my head.
A lot of stuff jumps in there but I usually have problems separating the
good from the bad.

The behavior is not "by design", but it is to be expected.  SQL Server
2000 was written using a C++ code base, and I'm assuming that you are
using VB.NET.  It would be interesting to see if SQL Server 2005 and VB
2005 will come up with the same reults using RAND (since they're built
on the .NET 2.0 framework).

You'll probably have to roll your own seeded randomizer.

Stu

Compute sum of count(*) with group by
Copying records from one table to another
Automate Importing File
concurrency in stored proc
Cursor working in this stored Procedure??