Generate Random Numbers using R Script on SQL Server

For database developers it is possible to use R script on SQL Server to generate random numbers. With SQL Server 2016 and SQL Server 2017 Machine Learning Services (either In-Database or as Standalone), data engineers are able to run R scripts and Python (new in SQL Server 2017) within T-SQL codes.
R services and Python provides powerful libraries for especially for data analytics. In this R in SQL tutorial, I will share how to create random numbers or generate random integer on SQL Server using R-services.

If you are familiar with executing R scripts on SQL Server, for random number generation you can refer to Uniform Distribution functions, random articles for details.

Following R Script generates a random number between 0 and 1.


EXEC sp_execute_external_script

 @language = N'R',

 @script = N'OutputDataSet <- as.data.frame( runif(1) );';

Code

create random number on SQL Server using R Services

In fact, RUNIF function takes 3 arguments. In above R code we have only used one parameter which is equal to 1.
This input argument enables RUNIF function to create that amount of numbers to be generated as output.

For example, if you modify above R in SQL script as follows and execute it, you will have 3 random numbers generated between 0 and 1.


EXEC sp_execute_external_script

 @language = N'R',

 @script = N'OutputDataSet <- as.data.frame( runif(3) );';

Code

create multiple random numbers on SQL Server with R RUNIF function

Other parameters are minimum and maximum values defines the range boundaries where the random number will be created within.
If SQL developer executes following sample code, 4 random numbers will be generated between 0 and 10.


EXEC sp_execute_external_script

 @language = N'R',

 @script = N'OutputDataSet <- as.data.frame( runif(4,0,10) );';

Code

random number generator

Below R script executed using sp_execute_external_script in SQL generates a random number and returns this randomly created number using an OUTPUT parameter.


declare @SQLVariable float;

EXEC sp_execute_external_script

 @language = N'R',

 @script = N'outputVar <- runif(1) ;',

 @params=N'@outputVar float OUTPUT',

 @outputVar=@SQLVariable OUTPUT;

select @SQLVariable as [Random Number]

Code

output parameters with R-Script on SQL Server

Below code generates a random integer between desired two integers. For example, it is possible to create random number between 0 and 100 by setting variable values in below T-SQL code.


declare @RandomNumber int

declare @MinValue int = 0

declare @MaxValue int = 100



-- R Script Execution

declare @SQLVariable float;



EXEC sp_execute_external_script

 @language = N'R',

 @script = N'outputVar <- runif(1) ;',

 @params=N'@outputVar float OUTPUT',

 @outputVar=@SQLVariable OUTPUT;

-- (End of) R Script Execution

select ceiling( @MinValue + (@MaxValue - @MinValue) * @SQLVariable ) as myRandomNumber

Code

create random number between two integers

Another method of creating any number of random integers between a desired number range is as follows:


declare @SqlMinValue int = 0

declare @SqlMaxValue int = 100



-- R Script Execution

declare @SqlVariable float;



EXEC sp_execute_external_script

@language = N'R',

@script = N'outputVar <- runif(1, minValue, maxValue) ;',

@params=N'@minValue int, @maxValue int, @outputVar float OUTPUT',

@minValue=@SqlMinValue,

@maxValue=@SqlMaxValue,

@outputVar=@SqlVariable OUTPUT;

-- (End of) R Script Execution

select ceiling( @SqlVariable ) as myRandomNumber

Code

pass input parameter and read output parameter from R Script on SQL Server

As SQL Server data engineers can realize, I used input parameters and output parameters to pass and read data from SQL stored procedure sp_execute_external_script to execute parametric R script

I encapsulated previous SQL codes and R script which generate random number in a SQL stored procedure.


Create Procedure Generate_Random_Number_using_R

(

 @MinValue int,

 @MaxValue int,

 @RandomNumber int OUTPUT

)



as



-- R Script Execution

declare @SQLVariable float;



EXEC sp_execute_external_script

 @language = N'R',

 @script = N'outputVar <- runif(1) ;',

 @params=N'@outputVar float OUTPUT',

 @outputVar=@SQLVariable OUTPUT;

-- (End of) R Script Execution

set @RandomNumber=ceiling( @MinValue + (@MaxValue - @MinValue) * @SQLVariable )

go

Code

Here it is an example for SQL Server database developers showing how to call and execute SQL stored procedure to create random number


declare @RandomNumber int

exec Generate_Random_Number_using_R 0,100,@RandomNumber Output

select @RandomNumber as "Random Number"

Code

SQL stored procedure for random numbers