SQL Server- RAND() - range

random, sql-server-2012

Solution

`RAND` Returns a pseudo-random float value from 0 through 1, exclusive.

So `RAND() * 100000000` does exactly what you need. However assuming that every number between 1 and 99,999,999 does have equal probability then 99% of the numbers will likely be between the length of 7 and 8 as these numbers are simply more common.

+--------+-------------------+----------+------------+
| Length |       Range       |  Count   |  Percent   |
+--------+-------------------+----------+------------+
|      1 | 1-9               |        9 |  0.000009  |
|      2 | 10-99             |       90 |  0.000090  |
|      3 | 100-999           |      900 |  0.000900  |
|      4 | 1000-9999         |     9000 |  0.009000  |
|      5 | 10000-99999       |    90000 |  0.090000  |
|      6 | 100000-999999     |   900000 |  0.900000  |
|      7 | 1000000-9999999   |  9000000 |  9.000000  |
|      8 | 10000000-99999999 | 90000000 | 90.000001  |
+--------+-------------------+----------+------------+

Problem

I want to create random numbers between 1 and 99,999,999. I am using the following code: ``` SELECT CAST(RAND() * 100000000 AS INT) AS [RandomNumber] ``` However my results are always between the length of 7 and 8, which means that I never saw a value lower then 1,000,000. Is there any way to generate random numbers between a defined range?

Original source