Learn more about SQL Server tools

 

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

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


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



 






Enter your 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 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


 









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 


Get free SQL tips:

*Enter Code refresh code     



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

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

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.


Learn more about SQL Server tools