Create Your Own RANDBETWEEN Function in T-SQL

Problem

There are several functions that are included with Excel that I use on a regular basis that are not included with SQL Server. The RANDBETWEEN function is one of these. Is there a way to recreate this functionality in T-SQL code?

Solution

In this tip, we will demonstrate how to create a RANDBETWEEN function and discuss one of the challenges to using the T-SQL RAND() function within a user-defined function.

The RANDBETWEEN function in Excel has two parameters, the bottom inclusive boundary and the top inclusive boundary, and it returns a random integer within these inclusive boundaries.

We can write a SELECT query in T-SQL to return an integer between 25 and 75 inclusive as shown here.

It would seem straightforward to take the query above and place it inside a CREATE FUNCTION statement. However, as we see below we get an error message stating “Invalid use of a side-effecting operator ‘rand’ within a function.”

So why is RAND() a side-effecting operator? This is because the last random number issued must be tracked internally, thus the side-effect. Fortunately, there is a workaround for this problem. We can create a view that contains a random number as a column, and then within our function we can select that random number column from the view as shown here.

Before we declare that we are finished, we need to test this new function over a sizable volume of data to make sure our function is working correctly. In the T-SQL code below, we will create a table named tblRandBetweenTest and populate it with 10,000 rows. The GeneratedRandomValue column will be populated using our new RANDBETWEEN function.

After populating the table, we can run a query to get the minimum, maximum and average values for the GeneratedRandomValue column. We see that the minimum is 25, the maximum is 75 and the average is 50 which indicates our function is working correctly.

We can also run a query to get counts per value generated by the function.

The complete T-SQL code for this tip is displayed here.

-- create the view to store a random number
create view dbo.vRandomNumber
as
  select rand() as RandomNumber
go
-- create the function
create function dbo.randbetween(@bottom integer, @top integer)
returns integer
as
begin
  return (select cast(round((@top-@bottom)* RandomNumber +@bottom,0) as integer) from dbo.vRandomNumber)
end
go
-- test the function
select dbo.randbetween(25,75)
-- create a table to store values generated by the function 
CREATE TABLE dbo.tblRandBetweenTest(
   pkRandBetweenTest int identity (1,1) primary key,
   GeneratedRandomValue integer NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
go
-- test the function 10,000 times
declare @i as integer
set @i=1
while @i<=10000
begin
   insert into dbo.tblRandBetweenTest values (dbo.randbetween(25,75))
   set @i=@i+1
end
-- select the min, max and average
select 
min(GeneratedRandomValue) as min_x,
max(GeneratedRandomValue) as max_x,
avg(GeneratedRandomValue) as avg_x
from dbo.tblRandBetweenTest
go
-- get the population of the values
select GeneratedRandomValue, count(GeneratedRandomValue) as qty
from dbo.tblRandBetweenTest
group by GeneratedRandomValue
order by GeneratedRandomValue

Next Steps

You can test what happens with different seed values for the RAND() function. Also, the RANDBETWEEN function created above can be modified to return decimal values instead of integers. Finally, read more about using random data and SQL Server in the following tips from MSSQLTips.com.

• Different ways to get random data for SQL Server data sampling
• SQL Server random numerics data generation using CLR
• SQL Server stored procedure to generate random passwords
• Retrieving random data from SQL Server with TABLESAMPLE.

Dallas Snider

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.