Efficient Way Random Number Generator Script

Jump To Right Section Show

In this article, we want to discuss a nice function to random number generator script in SQL Server. Before we get started, if you want to know about split with any delimited string, please go through the following article: Update table from another table in SQL Server. There are many methods to generate random numbers in SQL Server. Let’s start.

Method 1: Generate Random Numbers (Int) between Rang


---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

---- Create the variables for the random number generation

DECLARE @Random INT;

DECLARE @Upper INT;

DECLARE @Lower INT

---- This will create a random number between 1 and 999

SET @Lower = 1 ---- The lowest random number

SET @Upper = 999 ---- The highest random number

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

SELECT @Random

Method 2: Generate Random Float Numbers


SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )

+ (DATEPART(ss, GETDATE()) * 1000 )

+ DATEPART(ms, GETDATE()) )

Method 3: Random Numbers Quick Scripts


---- Random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
---- Random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
---- Random integer BETWEEN 0 AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
---- Random integer BETWEEN 10 AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())

---- Random float from 0 up to 20 - [0, 20)

SELECT 20*RAND()

---- Random float from 10 up to 30 - [10, 30)

SELECT 10 + (30-10)*RAND()

---- Random integer BETWEEN 0 AND 20 - [0, 20]

SELECT CONVERT(INT, (20+1)*RAND())

---- Random integer BETWEEN 10 AND 30 - [10, 30]

SELECT 10 + CONVERT(INT, (30-10+1)*RAND())

Method 4: Random Numbers (Float, Int) Tables Based on Time


DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum

DECLARE @t TABLE( randnum float )

DECLARE @cnt INT; SET @cnt = 0

WHILE @cnt <=10000

BEGIN

SET @cnt = @cnt + 1

INSERT INTO @t

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )

+ (DATEPART(ss, GETDATE()) * 1000 )

+ DATEPART(ms, GETDATE()) )

END

SELECT randnum, COUNT(*)

FROM @t

GROUP BY randnum

Method 5: Random number on a per-row basis


---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber