Random Number Generator Script

Random Number Generator

There are many methods to generate random number in SQL Server.

Method 1 : Generate Random Numbers (Int) between Rang
---- Create the variables for the random number generationDECLARE @Random INT;DECLARE @Upper INT;DECLARE @Lower INT

---- This will create a random number between 1 and 999SET @Lower = 1 ---- The lowest random numberSET @Upper = 999 ---- The highest random numberSELECT @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 0AND 20 - [0, 20]SELECT CONVERT(INT, (20+1)*RAND())----random integer BETWEEN 10AND 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 = 0WHILE @cnt <=10000BEGIN
SET @cnt = @cnt + 1INSERT INTO @tSELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )END
SELECT randnum, COUNT(*)FROM @tGROUP 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 distributionSELECT randomNumber, COUNT(1) countOfRandomNumberFROM (SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumberFROM sysobjects) sampleGROUP BY randomNumberORDER BY randomNumber