"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
> >
> >
>
>

"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
>
>

"Huiyong Lau" <huiyong_lau@hotmail.com> wrote in message
news:uydjS3n3EHA.3820@TK2MSFTNGP11.phx.gbl...
> 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
>> > >
>> > >
>> >
>> >
>>
>>
>
>

"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
> > >
> > >
> >
> >
>
>