A More Versatile SQL Server Random Number Function
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?
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.
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
View all my tips