SQL Server- RAND() - range

Question

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?

Answer 1

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

Answer 2

I created a function that might help. You will need to send it the Rand() function for it to work.

CREATE FUNCTION [dbo].[RangedRand] ( @MyRand float ,@Lower bigint = 0 ,@Upper bigint = 999 ) RETURNS bigint AS BEGIN DECLARE @Random BIGINT

SELECT @Random = ROUND(((@Upper - @Lower) * @MyRand + @Lower), 0)

RETURN @Random

END

GO

--Here is how it works.

--Create a test table for Random values

CREATE TABLE #MySample ( RID INT IDENTITY(1,1) Primary Key ,MyValue bigint ) GO

---

-- Lets use the function to populate the value column

INSERT INTO #MySample (MyValue)

SELECT dbo.RangedRand(RAND(), 0, 100) GO 1000

---

-- Lets look at what we get.

SELECT RID, MyValue FROM #MySample --ORDER BY MyValue -- Use this "Order By" to see the distribution of the random values

---

-- Lets use the function again to get a random row from the table

DECLARE @MyMAXID int SELECT @MyMAXID = MAX(RID) FROM #MySample

SELECT RID, MyValue FROM #MySample WHERE RID = dbo.RangedRand(RAND(), 1, @MyMAXID)

---

DROP TABLE #MySample

--I hope this helps.