>>I just wana make a random id number based on4 digits-for
>>examples??
>>
>checksum(newid ()) is better than the rand() function.
>
Please provide instructions on the use of these functions in MS-
Access using the Jet engine.
Sorry, I did not notice that the thread was cross-posted between the
SQL Server and Access newsgroups. I assumed that since you posted in a
newsgroup for SQL Server, you wanted a solution for SQL Server. I have
no idea what might work in Access.
Erland Sommarskog <esquel@sommars kog.sewrote in
news:Xns994B62F EC60E1Yazorman@ 127.0.0.1:
Bob Quintal (rquintal@sPAmp atico.ca) writes:
>Erland Sommarskog <esquel@sommars kog.sewrote in
>news:Xns994AD9 33293B9Yazorman @127.0.0.1:
>>chico_yallin@ya hoo.com (chico_yallin@y ahoo.com) writes:
>>>I just wana make a random id number based on4 digits-for
>>>examples??
>>>
>>checksum(newi d()) is better than the rand() function.
>>
>Please provide instructions on the use of these functions in
>MS- Access using the Jet engine.
>
Sorry, I did not notice that the thread was cross-posted
between the SQL Server and Access newsgroups. I assumed that
since you posted in a newsgroup for SQL Server, you wanted a
solution for SQL Server. I have no idea what might work in
Access.
>
I didn't notice the cross-post either, and I don't know wether
your solutin would be acceptable to the original poster. I sure
would like the functionality of newid() in Access.
>I just want to make a random id number based on 4 digits <<
Since it for an identifier this implies that there are no duplicates
("sampling without replacement" if you want to look it up). And there
are only values 0000 thru 9999 available to you. Just generate a
table of 10,000 rows and pull one out as you need it. m
>checksum(newid ()) is better than the rand() function.
Hi Erland,
What exactly makes checksum(newid( )) better than rand() ?
>
Here is a practical reason:
>
select rand(), checksum(newid( ))
from (select n = 1 union all select 2 union all select 4) as x
>
I believe that there also issues with the randomness of rand(), although
I don't remember the exact details. Steve Kass knows the full story.
The rand() function is not random at all. It will give the next number
in from a repeatable sequence of numbers, based on the seed.
On Sun, 10 Jun 2007 18:42:28 +0200, Gert-Jan Strik wrote:
>[snip]
>>checksum(newi d()) is better than the rand() function.
>
Hi Erland,
>
What exactly makes checksum(newid( )) better than rand() ?
>>
>Here is a practical reason:
>>
> select rand(), checksum(newid( ))
> from (select n = 1 union all select 2 union all select 4) as x
>>
>I believe that there also issues with the randomness of rand(), although
>I don't remember the exact details. Steve Kass knows the full story.
>
>The rand() function is not random at all. It will give the next number
>in from a repeatable sequence of numbers, based on the seed.
Hi Gert-Jan,
Isn't that what all random number generators do?
And isn't newid() more or less the same (using a different seed and a
different algorithm to compute the next value, but still computing some
formula with a seed as input to get at a pseudo-random value?)
>>>chico_yallin @yahoo.com (chico_yallin@y ahoo.com) writes:
>>>I just wana make a random id number based on4 digits-for examples??
>>>
>>>checksum(new id()) is better than the rand() function.
>>
>Hi Erland,
>>
>What exactly makes checksum(newid( )) better than rand() ?
>
>Here is a practical reason:
>
select rand(), checksum(newid( ))
from (select n = 1 union all select 2 union all select 4) as x
Hi Erland,
Sorry, I should have been more clearer. I know that rand() is called
just once for a set-based query, returning the same value for each row.
In this case, the original poster wanted "a" random number, so I assumed
that one was enough - that's why I didn't understand why you preferred
newid().
>I believe that there also issues with the randomness of rand(), although
>I don't remember the exact details. Steve Kass knows the full story.
>
On Sun, 10 Jun 2007 18:42:28 +0200, Gert-Jan Strik wrote:
>
[snip]
>checksum(newid ()) is better than the rand() function.
Hi Erland,
What exactly makes checksum(newid( )) better than rand() ?
>
Here is a practical reason:
>
select rand(), checksum(newid( ))
from (select n = 1 union all select 2 union all select 4) as x
>
I believe that there also issues with the randomness of rand(), although
I don't remember the exact details. Steve Kass knows the full story.
The rand() function is not random at all. It will give the next number
in from a repeatable sequence of numbers, based on the seed.
>
Hi Gert-Jan,
>
Isn't that what all random number generators do?
AFAIK they all create pseudo random numbers. I don't know if they all
procedure the repeatable results, based on the seed.
And isn't newid() more or less the same (using a different seed and a
different algorithm to compute the next value, but still computing some
formula with a seed as input to get at a pseudo-random value?)
I doubt it. The newid() value has to be globally unique, which suggests
the function should never produce an 'old' value ever again.
When using rand(), you could expect the same values after a reseed, or
an SQL Server restart. The newid() function should not have such
behavior.
Last post was suitable , according to my little experience in SQL
Thanks All
Ch.Yallin
--CELKO-- :
I just want to make a random id number based on 4 digits <<
>
Since it for an identifier this implies that there are no duplicates
("sampling without replacement" if you want to look it up). And there
are only values 0000 thru 9999 available to you. Just generate a
table of 10,000 rows and pull one out as you need it. m
On Mon, 11 Jun 2007 01:24:34 +0200, Gert-Jan Strik wrote:
>And isn't newid() more or less the same (using a different seed and a
>different algorithm to compute the next value, but still computing some
>formula with a seed as input to get at a pseudo-random value?)
>
>I doubt it. The newid() value has to be globally unique, which suggests
>the function should never produce an 'old' value ever again.
Hi Gert-Jan,
Well, that definitely rules out newid() as a "good" pseudo random number
generator, then. A sequence of random numbers should have a chance to
hold duplicates.
Of course, checksum(newid( )) will include duplicates, but only someone
privy to the implementation details of both newid() and checksum() can
determine wether the non-repetition of newid() values affects the
randomness of checksum(newid( )). If I had a need for a good RNG, I'd
look further!
>When using rand(), you could expect the same values after a reseed, or
>an SQL Server restart. The newid() function should not have such
>behavior.
I wasn't aware that the seed is reset on server restart. Is this
documented anywhere, or just based on personal observation? Anyway, it's
easy to fix it by putting
SET @dummy = RAND(DATEDIFF(s , '20000101', CURRENT_TIMESTA MP))
in a stored procedure and run it on startup.
Comment