Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Returning Random Numbers in a SELECT statement

As it is implemented, the RAND() function in SQL Server doesn't let you return a different random number per row in your SELECT statement. For example, if you execute this:

SELECT Rand() as RandomNumber, *
FROM Northwind..Customers

You will see the same number over and over.  However, sometimes, you may want to return a randomly generated number per line in your SELECT.   Here's one way to do that, in SQL Server 2000, using a UDF.

First, we need to create a View that returns a single random number:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

The view is necessary because normally in a UDF we cannot use the rand() function, because that would make the function non-determistic.  We can trick the UDF to accepting a random number by using a View.

Once that is set up, we can then create our function:

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END


Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row:

SELECT dbo.RandNumber(), *
FROM Northwind..Customers

You can get even fancier in your RandNumber function by accepting a seed if you like, or allowing for range parameters like this:

CREATE FUNCTION RandNumber2(@Min int, @Max int)
RETURNS float
AS
 BEGIN
 RETURN @Min + (select RandNumber from RetRandNumber) * (@Max-@Min)
 END

Do whatever you need to do, depending on your needs.  If you have existing code that needs to generate random values per row in a table, you may have been forced to write it using cursors or an outside language like VB.  Hopefully, you will find this useful and may be able to re-write some of that using set-based operations in pure T-SQL.

I was surprised, to be honest, that SQL Server executed the function over and over for each line in the SELECT; a UDF is supposed to be deterministic by definition, and since no parameters are being passed on each call, in theory SQL Server should only need to execute the UDF once per SELECT.  However, SQL Server calls UDF over and over for each row, and that allows us to implement our custom RAND() function successfully.

Enjoy!

see also:

Print | posted on Monday, November 22, 2004 3:13 PM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: Returning Random Numbers in a SELECT statement

Jeff-

Have you tried this one yet?

SELECT CAST(CAST(newid() AS binary(4)) AS int)

It's pretty random, but needs work in controlling the range of values. Casting as binary(3), binary(2) etc. can help there.
11/22/2004 5:15 PM | robvolk
Gravatar

# re: Returning Random Numbers in a SELECT statement

ah! i hadn't thought of converting to binary! I was thinking of using newid() and maybe converting the hex values, but it seemed like too much work. Not bad at all -- I like it. The other thing I was thinking about was: I am not sure of the algorithm for generating a GUID -- it depends on how random the 4 bytes you are converting to an int are. I assume it should be well distributed, but it may not be -- i.e., based on some factors on your machine values x-y are very likely while values a-b are impossible, etc. But I have no idea for sure about it.

Good idea!
11/22/2004 9:46 PM | Jeff
Gravatar

# SQL, ottimizzazioni, numeri random e curiosit

11/23/2004 7:11 PM | Marco Russo
Gravatar

# re: Returning Random Numbers in a SELECT statement

I can't vouch for the distribution pattern, but there are a few tweaks you can make:

SELECT rand(cast(cast(newid() as binary(4)) as int)) * cast(cast(newid() as binary(4)) as int)

or

SELECT cast(newid() as binary(4)) ^ cast(substring(cast(newid() as binary(4)), 7,4) as int)

Variations like these are sure to mix the results up enough to be about as random as you can get.
11/23/2004 10:24 PM | robvolk
Gravatar

# re: Returning Random Numbers in a SELECT statement

not bad at all -- and who know if RAND() is evenly distributed anyway ???
11/24/2004 12:15 PM | Jeff S
Gravatar

# re: Returning Random Numbers in a SELECT statement

Actually the UDF is not deterministic. So that is why SQL Server evaluates the udf for each row.

uncomment the rand() and the udf will be NonDeterministic, and reasonably so.

create view vrandnumber with schemabinding as select /*rand()*/ 1 as randnumber
go
create function randnumber() returns float with schemabinding
as begin return (select randnumber from dbo.vrandnumber) end
go

select objectproperty(object_id('RandNumber'),'IsDeterministic') as Deterministic

drop function randnumber
drop view vrandnumber
go
11/29/2004 6:04 PM | Claes W
Gravatar

# re: Returning Random Numbers in a SELECT statement

Thanks, Claes.

Of course this UDF is non-deterministic, that's why it works! :)

My point was that UDF's by definition are supposed to ALWAYS be deterministic; that is how they are specified and implemented in SQL Server 2000. Which is why we must use the View to "trick" SQL server into compiling and creating this UDF at all -- if we try to use a function such as RAND() or GETDATE() within a UDF, SQL Server does not allow it.

So, therefore, if SQL Server only allows deterministic functions, then a function that accepts no arguments should in theory always return the same value in a given SELECT statement. And if that is true, then it should only be evaluated ONCE. But, of course, as I mention this is not the case (luckily; otherwise, this particular piece of code would not work).

So, thanks for the feedback, but I think you misinterpreted that particular part (or more likely I did not express things very clearly).
I hope this helped clear things up for you! If you have any other questions, let me know!

- Jeff
11/29/2004 6:42 PM | Jeff
Gravatar

# won't work on SQL 2005

This will not work on SQL Server 2005:
"Invalid use of side-effecting or time-dependent operator"

Do you know a workaround or better solution for SQL2005?
5/30/2005 8:46 PM | Mathias Raacke
Gravatar

# re: SQL 2005

Ah! It appears SQL 2005 is a bit smarter than SQL 2000 and harder to trick! I haven't gotten a chance to really dig deep into SQL 2005 just yet, I'm sure there will be a way to get this done.
6/1/2005 9:35 AM | Jeff
Gravatar

# re: Returning Random Numbers in a SELECT statement

try this it is a simple but effective way of generating random numbers in sql. select CAST((1000*rand())+1 AS INT)

Change the 1000 to any number to set a top limit to the numbers for you.
8/22/2005 9:41 AM | eeee
Gravatar

# re: Returning Random Numbers in a SELECT statement

>> try this it is a simple but effective way of generating random numbers in sql. select CAST((1000*rand())+1 AS INT)

Thanks for the feedback, eee, but you've got to read the article .... that doesn't work for a multi-row select as the value returned is the same for each row. That behavior, and ways to avoid it, is the entire point of my post.
8/22/2005 10:03 AM | Jeff
Gravatar

# re: Returning Random Numbers in a SELECT statement

Please tell me how youde ploy it as a managed UDF in sql2000.


like this ?sp_addextendedproc 'xp_UserDefinedFunctions', 'xp_UserDefinedFunctions.dll'

Thanks
5/10/2006 2:14 PM | nicolas
Gravatar

# re: Returning Random Numbers in a SELECT statement

In 2005 sp1, I'm finding a failure when trying to run replication; however, when I just excute the UDF it seems to work just fine.

So the data/random in view seems to still be alright, knock on keyboard.
6/5/2006 12:35 PM | Tim Smolka
Gravatar

# re: Returning Random Numbers in a SELECT statement

Try this in 2005 (or any other SQL):

RAND(CAST(NEWID() AS BINARY(6)))

As long as you provide a seed to the RAND function it becomes non deterministic and evaluates at every row. All that you have to do is make sure that your seed changes so that you get a new number during each call. You don't really have to worry about how random the seed is because the RAND function will take care of the randomization for you.
7/25/2007 4:25 PM | darkeagle03
Gravatar

# re: Returning Random Numbers in a SELECT statement

For those of you still wondering-This technique DOES work in SQL 2005.
9/8/2007 9:57 PM | Mike
Gravatar

# re: Returning Random Numbers in a SELECT statement

That works for me... thanks
10/26/2007 5:29 AM | Rod
Gravatar

# re: Returning Random Numbers in a SELECT statement

I could hug you all...this is much better than using a date to randomize
6/24/2008 3:56 PM | Josh
Gravatar

# re: Returning Random Numbers in a SELECT statement

Hi, I noticed when testing this function that the returned range of values includes the @Min value but excludes the @Max value. This may not be the effect that some would expect from the function.

To include both the @Min and @Max values in the returned range of random numbers, change "@Max-@Min" to "@Max+1-@Min" :

ALTER FUNCTION [dbo].[fnGetRandomNumber](@Min int, @Max int)
RETURNS float
AS
BEGIN
RETURN @Min + (select RandomNumber from vwRandomNumberGenerator ) * (@Max+1-@Min)
END
8/26/2008 8:17 AM | bretlowery
Gravatar

# re: Returning Random Numbers in a SELECT statement

Thanks dear its help me although there is another site i found which makes me clear on this topic.
http://kaniks.blogspot.com/search/label/generate%20random%20number%20from%20t-sql

i hope this will help to other as well
12/22/2008 1:52 AM | Nitin
Gravatar

# re: Returning Random Numbers in a SELECT statement

I am trying to do something similar, but, UPDATE one table's Names values with a table with sample names I'd like to make it random. Basically I'm trying to de-identify a patient database ith fake names. Here's my SQL, can anyone help!!!

CREATE VIEW vRandName
AS
(SELECT TOP (1) Name FROM dbo.TestNames ORDER BY NEWID() as RandName)

CREATE FUNCTION random_names()
returns nchar
AS
BEGIN
DECLARE @f nchar
SET @f = (SELECT TOP (1) Name
FROM dbo.TestNames
ORDER BY NEWID())
RETURN @f
END
go
UPDATE TBL_PATIENT
SET First_NAME = random_names()

David
2/6/2009 12:12 AM | David
Gravatar

# re: Returning Random Numbers in a SELECT statement

I use the below code to generate the unique/random id.

SELECT (DATENAME(year, getDate()) + CONVERT(VARCHAR(2),DATEPART(m, GETDATE())) + DATENAME(d, getDate()) + DATENAME(hh, getDate()) + DATENAME(mi, getDate()) + DATENAME(ss, getDate()) + DATENAME(ms, getDate()))
3/10/2009 1:50 PM | Praba
Gravatar

# re: Returning Random Numbers in a SELECT statement

I tested the command : RAND(CAST(NEWID() AS BINARY(6)))


The distribution of the output is certainly distributed uniform(0,1)

Perhaps the next step is to use this to develop an MCMC algorithm in SQL
3/25/2009 3:00 PM | Patrick McCann
Gravatar

# re: Returning Random Numbers in a SELECT statement

SELECT TOP 1 * FROM table
ORDER BY NEWID()
7/23/2009 9:39 AM | Mario
Gravatar

# re: Returning Random Numbers in a SELECT statement

Thank you, it works very well. Just one thing: in the second function, RetRandNumber should be VRandNumber instead
1/4/2010 4:57 PM | Andrea
Gravatar

# re: Returning Random Numbers in a SELECT statement

Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row
10/6/2010 9:44 AM | snow boots for women
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET