Video on how to create your own RANDBETWEEN function for SQL Server

By: Dallas Snider | Comments (2) | Related: More > Functions User Defined UDF

Problem / Solution

There are several functions that are included with Excel that are not included with SQL Server. One of these functions is the RANDBETWEEN function. This function allows you to return a random number between a lower and upper bound values. In this video tip, Dallas Snider will show how you can create your own T-SQL function to mimic the Excel RANDBETWEEN function.

Key Learning Items

Examples of the RANDBETWEEN function
Creating a T-SQL RANDBETWEEN function
Issues using the T-SQL RAND() function in a UDF and how to get around the it

Companion Tip

Click Here

About the author

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

Comments For This Article

Thursday, December 11, 2014 - 9:51:11 AM - J Marlin

I believe this will give you a better distribution:

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

Thursday, December 11, 2014 - 9:42:41 AM - J Marlin	Back To Top (35570)
The function is pretty good, but the @Top and @Bottom values are only generated half as often as the numbers in between. This is apparent in your video, and also if you continue to add more rows to your testing table.