Re: How to Gnerate a Random ID Number
Re: How to Gnerate a Random ID Number
am 09.06.2007 12:44:03 von Bob Quintal
"chico_yallin@yahoo.com" wrote in
news:1181388231.643515.94630@k79g2000hse.googlegroups.com:
> I just wana make a random id number based on4 digits-for
> examples??
>
> Thanks in Advance
>
> Ch.Yallin
>
>
see the help files on the rnd() function.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Re: How to Gnerate a Random ID Number
am 09.06.2007 12:44:03 von Bob Quintal
"chico_yallin@yahoo.com" wrote in
news:1181388231.643515.94630@k79g2000hse.googlegroups.com:
> I just wana make a random id number based on4 digits-for
> examples??
>
> Thanks in Advance
>
> Ch.Yallin
>
>
see the help files on the rnd() function.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
How to Gnerate a Random ID Number
am 09.06.2007 13:23:51 von chico_yallin
I just wana make a random id number based on4 digits-for examples??
Thanks in Advance
Ch.Yallin
Re: How to Gnerate a Random ID Number
am 09.06.2007 21:19:45 von Erland Sommarskog
chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
> I just wana make a random id number based on4 digits-for examples??
checksum(newid()) is better than the rand() function.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: How to Gnerate a Random ID Number
am 09.06.2007 21:19:45 von Erland Sommarskog
chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
> I just wana make a random id number based on4 digits-for examples??
checksum(newid()) is better than the rand() function.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: How to Gnerate a Random ID Number
am 09.06.2007 22:04:22 von Bob Quintal
Erland Sommarskog wrote in
news:Xns994AD933293B9Yazorman@127.0.0.1:
> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>> 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.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Re: How to Gnerate a Random ID Number
am 09.06.2007 22:04:22 von Bob Quintal
Erland Sommarskog wrote in
news:Xns994AD933293B9Yazorman@127.0.0.1:
> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>> 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.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Re: How to Gnerate a Random ID Number
am 09.06.2007 22:45:29 von Hugo Kornelis
On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
>chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>> I just wana make a random id number based on4 digits-for examples??
>
>checksum(newid()) is better than the rand() function.
Hi Erland,
What exactly makes checksum(newid()) better than rand() ?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 09.06.2007 23:19:25 von Erland Sommarskog
Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
>
>>chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>> I just wana make a random id number based on4 digits-for examples??
>>
>>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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: How to Gnerate a Random ID Number
am 09.06.2007 23:57:08 von Bob Quintal
Tom van Stiphout wrote in
news:kpam63tiioev90uro1sp0iatnj56cncvnh@4ax.com:
> On 09 Jun 2007 20:04:22 GMT, Bob Quintal
> wrote:
>
> I think he was assuming a SQL Server back-end.
> -Tom.
>
I figured as much. But it would be nice to have a decent newid()
function in Access.
>>Erland Sommarskog wrote in
>>news:Xns994AD933293B9Yazorman@127.0.0.1:
>>
>>> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>>> 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.
>>
>>--
>>Bob Quintal
>>
>>PA is y I've altered my email address.
>
>
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Re: How to Gnerate a Random ID Number
am 10.06.2007 00:35:56 von Tom van Stiphout
On 09 Jun 2007 20:04:22 GMT, Bob Quintal
wrote:
I think he was assuming a SQL Server back-end.
-Tom.
>Erland Sommarskog wrote in
>news:Xns994AD933293B9Yazorman@127.0.0.1:
>
>> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>> 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.
>
>--
>Bob Quintal
>
>PA is y I've altered my email address.
Re: How to Gnerate a Random ID Number
am 10.06.2007 09:42:31 von Erland Sommarskog
Bob Quintal (rquintal@sPAmpatico.ca) writes:
> Erland Sommarskog wrote in
> news:Xns994AD933293B9Yazorman@127.0.0.1:
>> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>> 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, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: How to Gnerate a Random ID Number
am 10.06.2007 09:42:31 von Erland Sommarskog
Bob Quintal (rquintal@sPAmpatico.ca) writes:
> Erland Sommarskog wrote in
> news:Xns994AD933293B9Yazorman@127.0.0.1:
>> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>> 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, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Re: How to Gnerate a Random ID Number
am 10.06.2007 13:12:43 von Bob Quintal
Erland Sommarskog wrote in
news:Xns994B62FEC60E1Yazorman@127.0.0.1:
> Bob Quintal (rquintal@sPAmpatico.ca) writes:
>> Erland Sommarskog wrote in
>> news:Xns994AD933293B9Yazorman@127.0.0.1:
>>> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>>> 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.
>
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.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Re: How to Gnerate a Random ID Number
am 10.06.2007 13:12:43 von Bob Quintal
Erland Sommarskog wrote in
news:Xns994B62FEC60E1Yazorman@127.0.0.1:
> Bob Quintal (rquintal@sPAmpatico.ca) writes:
>> Erland Sommarskog wrote in
>> news:Xns994AD933293B9Yazorman@127.0.0.1:
>>> chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>>> 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.
>
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.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Re: How to Gnerate a Random ID Number
am 10.06.2007 17:39:40 von Joe 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
Re: How to Gnerate a Random ID Number
am 10.06.2007 17:39:40 von Joe 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
Re: How to Gnerate a Random ID Number
am 10.06.2007 18:42:28 von Gert-Jan Strik
[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.
For more details, check out
http://groups.google.nl/group/microsoft.public.sqlserver.pro gramming/browse_thread/thread/b7d0e057887380ca/0c1ff6c9523c1 9f0
(url may wrap)
Gert-Jan
Re: How to Gnerate a Random ID Number
am 11.06.2007 01:00:20 von Hugo Kornelis
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?
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?)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 11.06.2007 01:02:19 von Hugo Kornelis
On Sat, 9 Jun 2007 21:19:25 +0000 (UTC), Erland Sommarskog wrote:
>Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
>> On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
>>
>>>chico_yallin@yahoo.com (chico_yallin@yahoo.com) writes:
>>>> I just wana make a random id number based on4 digits-for examples??
>>>
>>>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
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.
Let's hope he chimes in, then.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 11.06.2007 01:24:34 von Gert-Jan Strik
See inline
Hugo Kornelis wrote:
>
> 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.
Gert-Jan
Re: How to Gnerate a Random ID Number
am 11.06.2007 21:58:37 von chico_yallin
Thanks all
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
Re: How to Gnerate a Random ID Number
am 11.06.2007 21:58:37 von chico_yallin
Thanks all
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
Re: How to Gnerate a Random ID Number
am 14.06.2007 01:18:20 von Hugo Kornelis
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_TIMESTAMP))
in a stored procedure and run it on startup.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 14.06.2007 01:29:09 von Hugo Kornelis
On Mon, 11 Jun 2007 01:02:19 +0200, Hugo Kornelis wrote:
> I know that rand() is called
>just once for a set-based query, returning the same value for each row.
Which, BTW, can be overcome in SQL Server 2005 using a dirty trick:
SELECT o.name, r.rnd
FROM sys.objects AS o
CROSS APPLY (SELECT RAND(CHECKSUM(o.name) ^ CHECKSUM(newid())) AS rnd)
AS r
The CHECKSUM(o.name) makes sure that the RAND function has to be called
for each row in sys.objects. With just this, the query would become
deterministic; this is overcome by also factoring in CHECKSUM(NEWID()).
Both CHECKSUM values can span the entire integer range; combining them
with bitwise exclusive OR results in a new integer that also spans the
entire range of integers. (Bitwise inclusive OR favors values with many
bits said; bitwise AND favors values with many bits off; adding or
subtracting runs the risk of exceeding the integer domain; and
subtracting the absolute values favors values around 0).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 14.06.2007 21:05:28 von Gert-Jan Strik
Hugo Kornelis wrote:
>
> 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.
Good observation. And so you correctly concluded that RAND() also does
not do this.
> 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!
Should you find a better (and practical) method, please share it :-)
> >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?
Oops... My apologies, that was a bit thoughtless of me. I merely
assumed the seed would be reset upon restart. However, I just tested
this on SQL Server 2005, and the seed does not seem to be reset (or at
least not to the same value).
Gert-Jan
> Anyway, it's
> easy to fix it by putting
> SET @dummy = RAND(DATEDIFF(s, '20000101', CURRENT_TIMESTAMP))
> in a stored procedure and run it on startup.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 15.06.2007 00:18:58 von Hugo Kornelis
On Thu, 14 Jun 2007 21:05:28 +0200, Gert-Jan Strik wrote:
(snip)
>> 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.
>
>Good observation. And so you correctly concluded that RAND() also does
>not do this.
Hi Gert-Jan,
Am I reading you incorrectly, or are you saying that the sequence of
numbers generated by RAND() never produces the same value twice?
>> 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!
>
>Should you find a better (and practical) method, please share it :-)
Heh! I've never yet had to implement a good RNG in SQL Server (or
anywhere, for that matter), but I do know that there's tons of
information on this subject on web pages and in books, so that's where
I'd start.
With the CLR, it's probably a lot easier to implement the RNG algorithm
of choice than it was before.
>> >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?
>
>Oops... My apologies, that was a bit thoughtless of me. I merely
>assumed the seed would be reset upon restart. However, I just tested
>this on SQL Server 2005, and the seed does not seem to be reset (or at
>least not to the same value).
Probably some value derived from an internal clock or something. Most
systems that have support for builtin random number generation use that
for their initial seed.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 15.06.2007 18:14:07 von Gert-Jan Strik
> >> 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.
> >
> >Good observation. And so you correctly concluded that RAND() also does
> >not do this.
>
> Am I reading you incorrectly, or are you saying that the sequence of
> numbers generated by RAND() never produces the same value twice?
No, I am not saying that. It might, I haven't analyzed the algorithm
thoroughly. But that doesn't matter. A good pseudo random number
generator should incorporate the idea that in a range of 2 billion
values, there is a one in 2 billion chance that the same value is
selected next. And after that, then again there is a one in 2 billion
chance it will appear again. And that is something the algorithm doesn't
do. The algorithm is totally deterministic.
Gert-Jan
Re: How to Gnerate a Random ID Number
am 15.06.2007 22:41:38 von Seribus Dragon
I know this is an odd question but I thought the new access was actual
just a frount end to the destop version of SQLSERVER?
Re: How to Gnerate a Random ID Number
am 15.06.2007 22:41:38 von Seribus Dragon
I know this is an odd question but I thought the new access was actual
just a frount end to the destop version of SQLSERVER?
Re: How to Gnerate a Random ID Number
am 16.06.2007 00:23:51 von Hugo Kornelis
On Fri, 15 Jun 2007 18:14:07 +0200, Gert-Jan Strik wrote:
>> >> 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.
>> >
>> >Good observation. And so you correctly concluded that RAND() also does
>> >not do this.
>>
>> Am I reading you incorrectly, or are you saying that the sequence of
>> numbers generated by RAND() never produces the same value twice?
>
>No, I am not saying that. It might, I haven't analyzed the algorithm
>thoroughly. But that doesn't matter. A good pseudo random number
>generator should incorporate the idea that in a range of 2 billion
>values, there is a one in 2 billion chance that the same value is
>selected next. And after that, then again there is a one in 2 billion
>chance it will appear again. And that is something the algorithm doesn't
>do. The algorithm is totally deterministic.
Hi Gert-Jan,
Any pseudoRNG will always have a deterministic algorithm; the only
alternative would be some device that measures some physical magnitude
that is deemed to be random enough. And both philosophers and physicists
would probably argue whether even that is truly random.
Anyway, a deterministic algorithm can still satisfy the 1 in 2 billion
chance of producing the occasional duplicate. I'll try to illustrate
with a simplified example, using lower numbers (to save me the hassle of
translating Dutch words for extremely high numbers to English, and you
the hassle of translating them back :-)
Let's say that we have an algorith to produce random numbers between 1
and 64. We do of course not want to limit the seed to that range of 64
numbers - instead we use an integer to store the seed, giving us a range
of over 4 billion different seed values. We use an algorithm to
calculate next seed from the previous seed in such a way that there
won't be any obvious pattern to the series and that all 4-and-a-bit
billion possible values are calculated once before the series starts
over. We then use an other algorithm to hash each of the possible
integer values into a number between 1 and 64, such that there will be
an equal distribution but (again) no obvious pattern.
Even though the algorithm is entirely deterministic, if you write down
the full sequence of 4,294,967,296 numbers this algorithm generates
before starting over, you will see a 1 in 64 chance of getting the same
number twice in a row, a 1 in 64^2 chance of getting the same number
thrice and a 1 in 64^3 chance of getting four equal numbers in a row.
The chance for getting five equals in a row might be somewhat more or
less than 1 in 64^4, and the chance of six in a row will definitely
differ significantly from 1 in 64^5. These issues can be fixed by
increasing the ratio of seed numbers vs generated values (e.g. by using
biging instead of int for the seed).
Note that nothing of the above necessarily applies to the random number
generation by SQL Server. This applies to random number generation in
general - as far as I know, the random number generator in SQL Server is
not described in detail, so only MS employees are able to tell if it's
implemented as described here, or in another way.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 16.06.2007 01:09:37 von Hugo Kornelis
On Fri, 15 Jun 2007 15:41:38 -0500, Seribus Dragon wrote:
>I know this is an odd question but I thought the new access was actual
>just a frount end to the destop version of SQLSERVER?
Hi Seribus,
No, it's not. You can *use* Access as a front-end to many different
DB's, including the Desktop Engine, but it also comes with it's own
builtin "database engine". (I enclose that in quotes because it lacks
some of the features of truly high-end relational database engines).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Re: How to Gnerate a Random ID Number
am 16.06.2007 01:09:37 von Hugo Kornelis
On Fri, 15 Jun 2007 15:41:38 -0500, Seribus Dragon wrote:
>I know this is an odd question but I thought the new access was actual
>just a frount end to the destop version of SQLSERVER?
Hi Seribus,
No, it's not. You can *use* Access as a front-end to many different
DB's, including the Desktop Engine, but it also comes with it's own
builtin "database engine". (I enclose that in quotes because it lacks
some of the features of truly high-end relational database engines).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis