Eric, Is there a limit to the RAND function of SQLServer 6.5. The guys of another application for the same telecomms company I work for said that after getting 32,434 unique values generated by this RAND function, the numbers already duplicate themselves. Is there anything that needs to be done to maintain the uniqueness?

Thanks for your response in advance.

Chelo

-- Anonymous, March 08, 2000

Answers

Chelo,

The SQL Server Books Online article, "Using RAND" states that the rand function is similar to the C run-time library rand function. Postings in comp.lang.c point out that modern implementations of the C rand function have a period (number of unique values) of at least 4 billion (2^^32). Indeed, my testing finds that the period of the SQL Server 7.0 rand function is > 400,000. I determined this by making repeated calls to rand (). I don't plan to test if the period is > 4 billion.

The other guys in your company may have called rand repeatedly with a seed. If the seed repeats after 32,434 unique seed values, then rand (seed) will too. The way the RAND function works, rand (seed) = rand (seed).

The proper way to use rand is to call it once, either with or without a seed. (If you don't provide a seed, SQL Server will choose one.) Then after that, call rand without a seed.

Hope this helps,

Eric

-- Anonymous, March 08, 2000