By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,833 Members | 2,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,833 IT Pros & Developers. It's quick & easy.

How to Gnerate a Random ID Number

P: n/a
I just wana make a random id number based on4 digits-for examples??

Thanks in Advance

Ch.Yallin

Jun 9 '07 #1
Share this Question
Share on Google+
21 Replies


P: n/a
"ch**********@yahoo.com" <ch**********@yahoo.comwrote in
news:11*********************@k79g2000hse.googlegro ups.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

Jun 9 '07 #2

P: n/a
ch**********@yahoo.com (ch**********@yahoo.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 9 '07 #3

P: n/a
On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
>ch**********@yahoo.com (ch**********@yahoo.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
Jun 9 '07 #4

P: n/a
Erland Sommarskog <es****@sommarskog.sewrote in
news:Xn**********************@127.0.0.1:
ch**********@yahoo.com (ch**********@yahoo.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

Jun 9 '07 #5

P: n/a
Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:
On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
>>ch**********@yahoo.com (ch**********@yahoo.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() ?
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 9 '07 #6

P: n/a
Bob Quintal (rq******@sPAmpatico.ca) writes:
Erland Sommarskog <es****@sommarskog.sewrote in
news:Xn**********************@127.0.0.1:
>ch**********@yahoo.com (ch**********@yahoo.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 10 '07 #7

P: n/a
Erland Sommarskog <es****@sommarskog.sewrote in
news:Xn**********************@127.0.0.1:
Bob Quintal (rq******@sPAmpatico.ca) writes:
>Erland Sommarskog <es****@sommarskog.sewrote in
news:Xn**********************@127.0.0.1:
>>ch**********@yahoo.com (ch**********@yahoo.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.
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

Jun 10 '07 #8

P: n/a
>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
Jun 10 '07 #9

P: n/a
[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
http://groups.google.nl/group/micros...1ff6c9523c19f0
(url may wrap)

Gert-Jan
Jun 10 '07 #10

P: n/a
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?

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
Jun 10 '07 #11

P: n/a
On Sat, 9 Jun 2007 21:19:25 +0000 (UTC), Erland Sommarskog wrote:
>Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:
>On Sat, 9 Jun 2007 19:19:45 +0000 (UTC), Erland Sommarskog wrote:
>>>ch**********@yahoo.com (ch**********@yahoo.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() ?

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
Jun 10 '07 #12

P: n/a
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
Jun 10 '07 #13

P: n/a
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
Jun 11 '07 #14

P: n/a
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
Jun 13 '07 #15

P: n/a
On Mon, 11 Jun 2007 01:02:19 +0200, Hugo Kornelis wrote:
I know that rand() is called
just once for a set-based query, returning the same value for each row.
Which, BTW, can be overcome in SQL Server 2005 using a dirty trick:

SELECT o.name, r.rnd
FROM sys.objects AS o
CROSS APPLY (SELECT RAND(CHECKSUM(o.name) ^ CHECKSUM(newid())) AS rnd)
AS r

The CHECKSUM(o.name) makes sure that the RAND function has to be called
for each row in sys.objects. With just this, the query would become
deterministic; this is overcome by also factoring in CHECKSUM(NEWID()).
Both CHECKSUM values can span the entire integer range; combining them
with bitwise exclusive OR results in a new integer that also spans the
entire range of integers. (Bitwise inclusive OR favors values with many
bits said; bitwise AND favors values with many bits off; adding or
subtracting runs the risk of exceeding the integer domain; and
subtracting the absolute values favors values around 0).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jun 13 '07 #16

P: n/a
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
Jun 14 '07 #17

P: n/a
On Thu, 14 Jun 2007 21:05:28 +0200, Gert-Jan Strik wrote:

(snip)
>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.
Hi Gert-Jan,

Am I reading you incorrectly, or are you saying that the sequence of
numbers generated by RAND() never produces the same value twice?
>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 :-)
Heh! I've never yet had to implement a good RNG in SQL Server (or
anywhere, for that matter), but I do know that there's tons of
information on this subject on web pages and in books, so that's where
I'd start.

With the CLR, it's probably a lot easier to implement the RNG algorithm
of choice than it was before.
>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).
Probably some value derived from an internal clock or something. Most
systems that have support for builtin random number generation use that
for their initial seed.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jun 14 '07 #18

P: n/a
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.

Am I reading you incorrectly, or are you saying that the sequence of
numbers generated by RAND() never produces the same value twice?
No, I am not saying that. It might, I haven't analyzed the algorithm
thoroughly. But that doesn't matter. A good pseudo random number
generator should incorporate the idea that in a range of 2 billion
values, there is a one in 2 billion chance that the same value is
selected next. And after that, then again there is a one in 2 billion
chance it will appear again. And that is something the algorithm doesn't
do. The algorithm is totally deterministic.

Gert-Jan
Jun 15 '07 #19

P: n/a
I know this is an odd question but I thought the new access was actual
just a frount end to the destop version of SQLSERVER?
Jun 15 '07 #20

P: n/a
On Fri, 15 Jun 2007 18:14:07 +0200, Gert-Jan Strik wrote:
>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.

Am I reading you incorrectly, or are you saying that the sequence of
numbers generated by RAND() never produces the same value twice?

No, I am not saying that. It might, I haven't analyzed the algorithm
thoroughly. But that doesn't matter. A good pseudo random number
generator should incorporate the idea that in a range of 2 billion
values, there is a one in 2 billion chance that the same value is
selected next. And after that, then again there is a one in 2 billion
chance it will appear again. And that is something the algorithm doesn't
do. The algorithm is totally deterministic.
Hi Gert-Jan,

Any pseudoRNG will always have a deterministic algorithm; the only
alternative would be some device that measures some physical magnitude
that is deemed to be random enough. And both philosophers and physicists
would probably argue whether even that is truly random.

Anyway, a deterministic algorithm can still satisfy the 1 in 2 billion
chance of producing the occasional duplicate. I'll try to illustrate
with a simplified example, using lower numbers (to save me the hassle of
translating Dutch words for extremely high numbers to English, and you
the hassle of translating them back :-)

Let's say that we have an algorith to produce random numbers between 1
and 64. We do of course not want to limit the seed to that range of 64
numbers - instead we use an integer to store the seed, giving us a range
of over 4 billion different seed values. We use an algorithm to
calculate next seed from the previous seed in such a way that there
won't be any obvious pattern to the series and that all 4-and-a-bit
billion possible values are calculated once before the series starts
over. We then use an other algorithm to hash each of the possible
integer values into a number between 1 and 64, such that there will be
an equal distribution but (again) no obvious pattern.

Even though the algorithm is entirely deterministic, if you write down
the full sequence of 4,294,967,296 numbers this algorithm generates
before starting over, you will see a 1 in 64 chance of getting the same
number twice in a row, a 1 in 64^2 chance of getting the same number
thrice and a 1 in 64^3 chance of getting four equal numbers in a row.
The chance for getting five equals in a row might be somewhat more or
less than 1 in 64^4, and the chance of six in a row will definitely
differ significantly from 1 in 64^5. These issues can be fixed by
increasing the ratio of seed numbers vs generated values (e.g. by using
biging instead of int for the seed).

Note that nothing of the above necessarily applies to the random number
generation by SQL Server. This applies to random number generation in
general - as far as I know, the random number generator in SQL Server is
not described in detail, so only MS employees are able to tell if it's
implemented as described here, or in another way.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jun 15 '07 #21

P: n/a
On Fri, 15 Jun 2007 15:41:38 -0500, Seribus Dragon wrote:
>I know this is an odd question but I thought the new access was actual
just a frount end to the destop version of SQLSERVER?
Hi Seribus,

No, it's not. You can *use* Access as a front-end to many different
DB's, including the Desktop Engine, but it also comes with it's own
builtin "database engine". (I enclose that in quotes because it lacks
some of the features of truly high-end relational database engines).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jun 15 '07 #22

This discussion thread is closed

Replies have been disabled for this discussion.