SQL Server - Random Number Generator
SQL SERVER - Random Number Generator Script - SQL Query
Apr 29th, 2007 by pinaldave
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
Method 2 : Generate Random Float Numbers
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())
Method 4 : Random Numbers (Float, Int) Tables Based with 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
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