Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Create Your Own RANDBETWEEN Function in T-SQL


By:   |   Read Comments (5)   |   Related Tips: More > Functions - User Defined UDF

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.

Create Your Own RANDBETWEEN Function in T-SQL

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

We can write a SELECT query in T-SQL

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."

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.

So why is RAND() a side-effecting operator?

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.

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 run a query to get the minimum, maximum and average values

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

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.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, December 12, 2014 - 9:29:49 AM - J Marlin Back To Top

The function is pretty good, but the @Top and @Bottom values are only generated half as often as the numbers in between, due to rounding issues.

The following modification appears to give a much a better distribution on the first and last members of the set.

CREATE FUNCTION RandBetween
    ( @Bottom INTEGER
    , @Top INTEGER )
RETURNS INTEGER
AS
BEGIN
    RETURN ( SELECT CAST( FLOOR( (@Top - @Bottom + 1) * RandomNumber + @Bottom ) AS INTEGER) FROM dbo.vwRandom
    )
END


Monday, August 11, 2014 - 1:15:53 PM - Eric Back To Top

After doing a few runs with the code provided (but doing between 100 and 400 with 50,000 runs) there appears to be an anomoly at the max and min bounds. Both (100 and 400) after 5 runs were quite low compared to the other 298 columns (numbers). It appears that at the boundries there is a problem. The middle of the range is fine and appears to be what is expected. Not sure what might be happening here. 

 


Monday, August 04, 2014 - 11:45:05 PM - Arsen Barbakadze Back To Top

Hi hardik kumar rawal,

In original code we have:

 return (select cast(round((@top-@bottom)* RandomNumber +@bottom,0) as integer) from dbo.vRandomNumber)
 
and you when copied it and created your function missed last part of this statement:
return(select CAST(round((@top-@bottom)* RandomNumber + @bottom,0) as int))
So, the original code is correct and works perfect.
Thanks for very useful tip!

Monday, August 04, 2014 - 9:36:15 AM - krismaly Back To Top

Wow! this is really useful in working. Thanks for educating the community and your volunteer-ship is appreciated. Please keep publish on other functions with examples. Thanks a bunch


Monday, August 04, 2014 - 2:44:53 AM - hardik kumar rawal Back To Top

hi,

 This post is very usefull to all DB Developers.

But I saw above post, and trying on my local SQL SERVER, and I found error 

----your code----

Create view dbo.vRandomNumber

as 

select RAND() as RandomNumber

go

 

create function dbo.randbetween(@bottom int, @top int)

returns int

as

begin 

return(select CAST(round((@top-@bottom)* RandomNumber + @bottom,0) as int))

End

go

 

select dbo.randbetween(25,75)

-------------error

Msg 207, Level 16, State 1, Procedure randbetween, Line 5

 

Invalid column name 'RandomNumber'.

----------


So I put sub query to solve this error.


Create view vRandomNumber

as 

select RAND() as RandomNumber

go

create function dbo.randbetween(@bottom int, @top int)

returns int

as

begin 

return(select CAST(round((@top-@bottom)* (select RandomNumber from vRandomNumber) + @bottom,0) as int))

End

go

select dbo.randbetween(25,75)

Its works properly.
so please check and crrect first one , if required 
 
Thanks.
 

Learn more about SQL Server tools