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?
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((@[email protected])* RandomNumber [email protected],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 @[email protected]+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
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.
Last Update: 2014-08-04
About the author
View all my tips