Re: Random number

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Huiyong Lau (huiyong_lau_at_hotmail.com)
Date: 12/10/04 

Date: Fri, 10 Dec 2004 17:02:57 +1100

Thought you need the random number to between 10000 and 99999.

Regards,
Huiyong

"Peter Afonin" <pva@speakeasy.net> wrote in message
news:e21cehn3EHA.3840@tk2msftngp13.phx.gbl...
> Thank you very much, Louis. That will work well for me.
>
> Peter
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:ujz$$Jn3EHA.924@TK2MSFTNGP14.phx.gbl...
> > select cast(rand() * 100000 as int) % 100000 will give you a five digit
> > number
> >
> > You could even make it a default:
> >
> > drop table testRand
> > go
> > create table testRand
> > (
> > testRandId int primary key default (cast(rand() * 1000000 as int) %
> > 1000000 ),
> > anotherColumn int
> > )
> > insert into testRand (anotherColumn) values (1)
> > insert into testRand (anotherColumn) values (2)
> >
> > select * from testRand
> >
> > Of course there is a 1/99999 chance you will get a collision and have to
> try
> > again :) Even if you check ahead of time, unless you use a serializable
> > transaction you will still have the chance that another user might
> randomly
> > choose the same value.
> >
> > What does it matter if it is in sequence? Identities are very weill
> > implemented. If I wasn't going to use identities, I would choose some
> > value that is implemented outside of SQL Server that uses the natural
key
> to
> > the table as a key, then possibly using a hash to calculate a number if
> you
> > want. but no matter what you do it is going to be tricky.
> >
> > --
>
> --------------------------------------------------------------------------
> --
> > Louis Davidson - drsql@hotmail.com
> > SQL Server MVP
> >
> > Compass Technology Management - www.compass.net
> > Pro SQL Server 2000 Database Design -
> > http://www.apress.com/book/bookDisplay.html?bID=266
> > Note: Please reply to the newsgroups only unless you are interested in
> > consulting services. All other replies may be ignored :)
> >
> > "Peter Afonin" <pva@speakeasy.net> wrote in message
> > news:Ocm%23Fwm3EHA.1564@TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > Is there a formula that generates the random number that I could put
in
> > > the
> > > table column as a default and use it as a primary key and an
alternative
> > > of
> > > identity column? The uniqueidentifier is too long for my needs, I'd
like
> > > to
> > > have a five-digit number. I could create an identity column, but I
don't
> > > want the numbers to be in sequence.
> > >
> > > I would appreciate your help.
> > >
> > > Thank you,
> > >
> > > --
> > > Peter Afonin
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Random number
    ... >> create table testRand ... If I wasn't going to use identities, ... >> value that is implemented outside of SQL Server that uses the natural ... but no matter what you do it is going to be tricky. ...
    (microsoft.public.sqlserver.programming)
  • Performance Issue (Winforms)
    ... attached to a sql server on another machine that's a server using Gigabit ... I tried disabling hyperthreading on the P4 and it didn't matter. ... Athlons) to run as fast as the Pentium M? ... Hell the 1.7 smokes the P4 when it's running debug code from ...
    (microsoft.public.dotnet.framework)
  • Re: Getting list of recently added IDENTITY items
    ... The main reason I use identities is for ease of use. ... but that would be hard to sell to the customers - 1 yr delay ... this issue in my mind using my original posted code since we can't also ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Partitioned View Problem
    ... > in the development phase your suggestion of inserting into the base tables ... > option if the database designer used identities. ... > without requiring views) will see SQL Server heading down the right track ... > Greg Linwood ...
    (microsoft.public.sqlserver.programming)
  • Re: Computer name question
    ... I did not do a swing today and I ... >SQL server I ... Will that matter to SQL server? ... can I change it to uppercase? ...
    (microsoft.public.backoffice.smallbiz2000)