Learn more about SQL Server tools

mssqltips logo

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

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

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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 Update:

next webcast button

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

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    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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

I believe this will give you a better distribution:

    ( @Bottom INTEGER
    , @Top INTEGER )
    RETURN ( SELECT CAST( FLOOR( (@Top - @Bottom + 1) * RandomNumber + @Bottom ) AS INTEGER) FROM dbo.vwRandom 

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