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.
Notice in the image above that each row in the result set has a differing value in the RandomNumber2 column.
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
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.
