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

By:   |   Comments (2)   |   Related: > 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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

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:

    ( @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 (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.

get free sql tips
agree to terms