A More Versatile SQL Server Random Number Function

By:   |   Comments (9)   |   Related: > TSQL


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

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.


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

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

CREATE FUNCTION dbo.myRandomNumberFunction()
    RETURN (SELECT randomNumber FROM dbo.myRandomNumberView);

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()
    RETURN (SELECT randomNumber*100 FROM dbo.myRandomNumberView);

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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

View all my tips

Comments For This Article

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

 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.



Tuesday, March 24, 2015 - 2:44:36 PM - Greg Robidoux Back To Top (36667)

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


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

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.


Tuesday, March 24, 2015 - 2:13:22 PM - jeff_yao Back To Top (36664)
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 (36652)

@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 (36646)

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

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

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 (36639)

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 (36632)

MIght interesting but I'm usinb for same goul following expressions



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


get free sql tips
agree to terms