How to Gnerate a Random ID Number

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • chico_yallin@yahoo.com

    How to Gnerate a Random ID Number

    I just wana make a random id number based on4 digits-for examples??

    Thanks in Advance

    Ch.Yallin

  • Bob Quintal

    #2
    Re: How to Gnerate a Random ID Number

    "chico_yallin@y ahoo.com" <chico_yallin@y ahoo.comwrote in
    news:1181388231 .643515.94630@k 79g2000hse.goog legroups.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

    Comment

    • Erland Sommarskog

      #3
      Re: How to Gnerate a Random ID Number

      chico_yallin@ya hoo.com (chico_yallin@y ahoo.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@sommarsk og.se

      Books Online for SQL Server 2005 at

      Books Online for SQL Server 2000 at

      Comment

      • Hugo Kornelis

        #4
        Re: How to Gnerate a Random ID Number

        On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
        >chico_yallin@y ahoo.com (chico_yallin@y ahoo.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

        Comment

        • Bob Quintal

          #5
          Re: How to Gnerate a Random ID Number

          Erland Sommarskog <esquel@sommars kog.sewrote in
          news:Xns994AD93 3293B9Yazorman@ 127.0.0.1:
          chico_yallin@ya hoo.com (chico_yallin@y ahoo.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

          Comment

          • Erland Sommarskog

            #6
            Re: How to Gnerate a Random ID Number

            Hugo Kornelis (hugo@perFact.R EMOVETHIS.info. INVALID) writes:
            On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
            >
            >>chico_yallin@ yahoo.com (chico_yallin@y ahoo.com) writes:
            >>I just wana make a random id number based on4 digits-for examples??
            >>
            >>checksum(newi d()) 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@sommarsk og.se

            Books Online for SQL Server 2005 at

            Books Online for SQL Server 2000 at

            Comment

            • Erland Sommarskog

              #7
              Re: How to Gnerate a Random ID Number

              Bob Quintal (rquintal@sPAmp atico.ca) writes:
              Erland Sommarskog <esquel@sommars kog.sewrote in
              news:Xns994AD93 3293B9Yazorman@ 127.0.0.1:
              >chico_yallin@ya hoo.com (chico_yallin@y ahoo.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@sommarsk og.se

              Books Online for SQL Server 2005 at

              Books Online for SQL Server 2000 at

              Comment

              • Bob Quintal

                #8
                Re: How to Gnerate a Random ID Number

                Erland Sommarskog <esquel@sommars kog.sewrote in
                news:Xns994B62F EC60E1Yazorman@ 127.0.0.1:
                Bob Quintal (rquintal@sPAmp atico.ca) writes:
                >Erland Sommarskog <esquel@sommars kog.sewrote in
                >news:Xns994AD9 33293B9Yazorman @127.0.0.1:
                >>chico_yallin@ya hoo.com (chico_yallin@y ahoo.com) writes:
                >>>I just wana make a random id number based on4 digits-for
                >>>examples??
                >>>
                >>checksum(newi d()) 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

                Comment

                • --CELKO--

                  #9
                  Re: How to Gnerate a Random ID Number

                  >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


                  Comment

                  • Gert-Jan Strik

                    #10
                    Re: How to Gnerate a Random ID Number

                    [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

                    (url may wrap)

                    Gert-Jan

                    Comment

                    • Hugo Kornelis

                      #11
                      Re: How to Gnerate a Random ID Number

                      On Sun, 10 Jun 2007 18:42:28 +0200, Gert-Jan Strik wrote:
                      >[snip]
                      >>checksum(newi d()) 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

                      Comment

                      • Hugo Kornelis

                        #12
                        Re: How to Gnerate a Random ID Number

                        On Sat, 9 Jun 2007 21:19:25 +0000 (UTC), Erland Sommarskog wrote:
                        >Hugo Kornelis (hugo@perFact.R EMOVETHIS.info. INVALID) writes:
                        >On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
                        >>
                        >>>chico_yallin @yahoo.com (chico_yallin@y ahoo.com) writes:
                        >>>I just wana make a random id number based on4 digits-for examples??
                        >>>
                        >>>checksum(new id()) 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

                        Comment

                        • Gert-Jan Strik

                          #13
                          Re: How to Gnerate a Random ID Number

                          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

                          Comment

                          • chico_yallin@yahoo.com

                            #14
                            Re: How to Gnerate a Random ID Number

                            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

                            Comment

                            • Hugo Kornelis

                              #15
                              Re: How to Gnerate a Random ID Number

                              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_TIMESTA MP))
                              in a stored procedure and run it on startup.

                              --
                              Hugo Kornelis, SQL Server MVP
                              My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

                              Comment

                              Working...