Posted by Hugo Kornelis on 06/14/07 22:18

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

[Back to original message]