Database

## Random Number Generator

Is there a way to generate a unique random number? It tried RANDOM(lower limit, upper limit) and it generated random numbers however there were repeats. I need a unique number for each row.

Thanks
4 REPLIES 4

## Re: Random Number Generator

Could you use a table to incr a counter and use that for each row? It wouldn't be random; however, it would provide for your uniqueness requirement.

## Re: Random Number Generator

You may have a larger range between lower and upper limits to create almost unique values ( generally 10 times of the rows). RANDOM function is activated for each row processed so you cannot guarantee unique values using random function.

## Re: Random Number Generator

Hi,

You can create Identity columns to make use of system generated uniqueness value.

Another simple way to generate unique number without specifying the upper limit is by using CSUM.

Eg:-
CSUM(1, sort_col)

Regards,
Balamurugan

## Re: Random Number Generator

The generator shown here is an implementation of the additive congruential method of generating values in pseudo-random order and is due to Roy Hann of Rational Commerce Limited, a CA-Ingres consulting
firm. It is based on a shift-register and an XOR-gate, and it has its origins in cryptography. While there are other ways to do this, this code is nice because:

1) The algorithm can be written in C or another low level language for speed. But math is fairly simple even in base ten.

2) The algorithm tends to generate successive values that are (usually) "far apart", which is handy for improving the performance of tree indexes. You will tend to put data on separate physical data pages in storage.

3) The algorithm does not cycle until it has generated every possible value, so we don't have to worry about duplicates. Just count how many calls have been made to the generator.

4) The algorithm produces uniformly distributed values, which is a nice mathematical property to have. It also does not include zero.

It might not be obvious that successive values are far apart when we are looking at a tiny 4-bit register. But it is clear that the values are generated in no obvious order, all possible values except 0 are eventually produced, and the termination condition is clear -- the Generator cycles back to 1.

Generalizing the algorithm to arbitrary binary word sizes, and therefore longer number sequences, is not as easy as you might think. Finding the "tap" positions where bits are extracted for feedback varies according to the word-size in an extremely non-obvious way. Choosing incorrect tap positions results in an incomplete and usually very short cycle, which is unusable. If you want the details and tap positions for words of one to 100 bits, see E. J. Watson, "Primitive Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962, p.368-369.

UPDATE Generator31
SET keyval =
keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)*2^30;

Or if you prefer, the algorithm in C:

int Generator31 ()
{static int n = 1;
n = n >> 1 | ((n^n >> 3) & 1) << 30;
return n;