|
|
Posted by Gert-Jan Strik on 06/14/07 19:05
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
Navigation:
[Reply to this message]
|