Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

A More Versatile SQL Server Random Number Function


By:   |   Read Comments (9)   |   Related Tips: More > T-SQL

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?

Built-in rand function not returning desired results
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.

Results from our 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.

Random number 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.



Last Update:






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.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Sunday, January 10, 2016 - 12:38:48 AM - Jeff Moden Back To Top

 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 Back To Top

Also, please check out Dallas' previous tip as well: http://www.mssqltips.com/sqlservertip/3529/sql-server-random-sorted-result-set/

Thanks
-Greg


Tuesday, March 24, 2015 - 2:40:27 PM - Greg Robidoux Back To Top

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.

Thanks
-Greg


Tuesday, March 24, 2015 - 2:13:22 PM - jeff_yao Back To Top
It seems we can have much easier way to achieve the same goal of this tip:

select rand(row_number() over (order by object_id)) from sys.all_columns



if you want really big number of rows, try this

select rand( row_number() over (order by c1.object_id) ) from sys.all_columns c1 cross joinsys.all_columns c2



To the original question in the tip, the following query will do the same work:

select rand(row_number() over (order by )) from testdata

i.e, should be included for clarity purpose.

Tuesday, March 24, 2015 - 8:58:25 AM - the sqlist Back To Top

@Jeff Moden

 

Your query fails:

SELECT RAND(NEWID()) FROM sys.all_columns;

Msg 206, Level 16, State 2, Line 1

Operand type clash: uniqueidentifier is incompatible with int

 

RAND takes n integer as parameter.


Monday, March 23, 2015 - 6:03:27 PM - Really Knowing SQL Back To Top

CRYPT_GEN_RANDOM would be your best (fast and easy) friend.


Monday, March 23, 2015 - 11:19:06 AM - Jeff Moden Back To Top

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.


Monday, March 23, 2015 - 9:21:34 AM - the sqlist Back To Top

Here is a much simpler solution:

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.


Monday, March 23, 2015 - 1:57:26 AM - Henn Sarv Back To Top

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 :)

 


Learn more about SQL Server tools