Posted by Hugo Kornelis on 06/13/07 23:18

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

[Back to original message]