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.
Ah... my apologies. First, I know it's an old post but I made a mistake (thanks to @the Sqllist for pointing it out) and had lost track of the post.
I posted that RAND(NEWID())) would work. Not sure what I did but I left out the "CHECKSUM" on that. The following is what I've used since I can remember.
SELECT RAND(CHECKSUM(NEWID()));
Tuesday, March 24, 2015 - 2:44:36 PM - Greg Robidoux
Thanks everyone for sharing your input about how to generate random numbers. It is good to see that there are several options for doing this. Keep up the discussion and continue to share your insights.
If you truly want to use RAND() in a query, there's no need for a function, especially a scalar function. You can use RAND() directly as demonstrated in the following.
SELECT RAND(NEWID()) FROM sys.all_columns;
I think it ironic that RAND() needs the random seed provided by NEWID() but it does work as expected. Obviously, you can add any multiplier and range offset that you need as you would for RAND() in other languages.
select CAST((ABS(CHECKSUM(NEWID())) % 9999)/100.00 AS decimal(5,2)) from testdata
Will return a 99.99 random value for each row. Of course you can control teh format values to be returned just by changing the 9999)/100.00 as decimal(5,2) to what you need.
MIght interesting but I'm usinb for same goul following expressions
example:
SELECT top (100)
ABS(CHECKSUM(NEWiD())) % 77 + 1
from sometable
gives me random 100 numbers from 1 to 77
THis is more flexible and performing ccreating quickly random sample data. For example with similar Query I create wit 2 selects new NORTHWIND sales data about 100+ million orders with average 10 rows in each. When interested - I can send my sample script :)
function.JPG)
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.