SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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) );';

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) );';

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) );';

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]

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

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

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

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"

SQL stored procedure for random numbers







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems