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


By:   |   Updated: 2014-09-11   |   Comments (2)   |   Related: More > Functions - User Defined UDF




 






Enter your business email address to
get free SQL Server tips.

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


Last Updated: 2014-09-11


get scripts

next tip button



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





Comments For This Article




Thursday, December 11, 2014 - 9:51:11 AM - J Marlin Back To Top (35571)

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.



download





Recommended Reading

Validate Integer and Decimal Values in SQL Server

Computed Columns with Scalar Functions SQL Server Performance Issue

Auto Generate Create Table Script Based on SQL Server Query

Four ways to improve scalar function performance in SQL Server

SQL Server User Defined Function Example








get free sql tips
agree to terms


Learn more about SQL Server tools