A More Versatile SQL Server Random Number Function

By: Dallas Snider   |   Updated: 2015-03-23   |   Comments (9)   |   Related: > TSQL

Problem

When I use the SQL Server RAND() T-SQL function as a column in a SELECT statement, the same value is returned for every row as shown below. How can I get differing random values on each row?

Solution

In this tip, we will show how to create your own user defined function in T-SQL that will return a random number for every row of your query. For this function to produce the desired results, we have to force the database engine to call the built-in RAND function once for each row. This will be accomplished by creating a view that contains a call to the RAND function, and then having our user defined function select from the view.

Let's begin by creating our view using the T-SQL CREATE VIEW statement below.

USE MSSQLTips
GO

CREATE VIEW dbo.myRandomNumberView
AS
  SELECT randomNumber = RAND();
GO

Next, we will define our function with the T-SQL below.

CREATE FUNCTION dbo.myRandomNumberFunction()
RETURNS DECIMAL(12,11)
AS
BEGIN
    RETURN (SELECT randomNumber FROM dbo.myRandomNumberView);
END
GO

Now, we are ready to test our random number function.

The data type returned by the function can be changed to meet your needs. For example, if you need a random amount less than 100 with 2 decimal places you could create the function as shown in the T-SQL below.

CREATE FUNCTION dbo.myRandomNumberFunction()
RETURNS DECIMAL(5,2)
AS
BEGIN
    RETURN (SELECT randomNumber*100 FROM dbo.myRandomNumberView);
END
GO

Notice how when we execute myRandomNumberFunction, the RandomNumber2 column contains amounts less than 100 with 2 decimal places.

Next Steps

Check out these other tips and tutorials on T-SQL and the RAND() function on MSSQLTips.com.

• Different ways to get random data for SQL Server data sampling
• Create Your Own RANDBETWEEN Function in T-SQL
• Generating Random Numbers in SQL Server Without Collisions
• SQL Server Random Sorted Result Set
• Our complete tutorial list

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.

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

View all my tips