Re: How to Gnerate a Random ID Number

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
.

Relevant Pages

  • Re: How to Gnerate a Random ID Number
    ... formula with a seed as input to get at a pseudo-random value?) ... that definitely rules out newid() as a "good" pseudo random number ... I wasn't aware that the seed is reset on server restart. ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Humor
    ... Hugo Kornelis wrote: ... SQL Server will often produce the same execution for both versions. ... the big questions for the RM, *for me* seem to include view updatability which has to do with the operators of the algebra as well as whether a relational engine can implement customary features such as concurrency control and presentation coherence without being written in a language that eschews the relational operators. ...
    (comp.databases.theory)
  • Re: Need help with hint syntax in SQL Server 2000
    ... unable to recognize that it shoul push the where clause condition down ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... Thank you both for the replies. ...
    (comp.databases.ms-sqlserver)
  • Re: Adding rows to a dataset where the primary key is type System.Guid
    ... NewID comes to mind. ... Dim x As Guid ... Hitchhiker's Guide to Visual Studio and SQL Server ... Is there an equivalent newid() in ADO.NET to create the guid within the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help with missing data in query
    ... I did go to that site But I couldn't figure out how to upload anything. ... GROUP BY m.MonthName WITH ROLLUP ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)