Seeding SQL RAND() Method With NEWID() For Per-Row Random Values (Thanks Joshua Cyr)
Posted January 27, 2009 at 2:54 PM by Ben Nadel
From what I have read and from what I've seen demos of, I thought that MS SQL's RAND() method could only return one random value per connection. As I never looked at the documentation for RAND(), I never realized that it could be seeded. This morning, in my post on selecting random records from a weighted data table, Joshua Cyr pointed me to a great blog post demonstrating how to get RAND() to produce random numbers for each record in a query.
Before we see that, however, let's take a look at how it won't work:
- <!--- Select random numbers. --->
- <cfquery name="qRandom" datasource="#REQUEST.DSN#">
- SELECT
- p.id,
- ( RAND() ) AS random_number
- FROM
- pivot1000 p
- WHERE
- p.id < 10
- ORDER BY
- p.id ASC
- </cfquery>
-
-
- <!--- Output the random numbers generated. --->
- <cfloop query="qRandom">
-
- #qRandom.random_number#<br />
-
- </cfloop>
Notice here that we are simply selecting RAND() as a column in the table, then output that column. When we run this code, we get the following:
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
Each call to RAND() produces the same result. This is because the RAND() method is implicitly seeded on the first call and then each subsequent call in the same connection uses the same seed value. To get a new random value for each RAND() method call, we have to pass it a new seed value each time.
The seed value must be numeric. Now, in the past, I've used NEWID() to create random numbers; but again, I never tried this on a per-row basis. As demonstrated in Josh's link, the NEWID() can be used to seed the RAND() method call every time it is run:
- <!--- Select random numbers. --->
- <cfquery name="qRandom" datasource="#REQUEST.DSN#">
- SELECT
- p.id,
-
- <!--- Seed RAND() with binary version of NEWID(). --->
- (
- RAND(
- CAST( NEWID() AS varbinary )
- )
- ) AS random_number
- FROM
- pivot1000 p
- WHERE
- p.id < 10
- ORDER BY
- p.id ASC
- </cfquery>
-
-
- <!--- Output the random numbers generated. --->
- <cfloop query="qRandom">
-
- #qRandom.random_number#<br />
-
- </cfloop>
As I have stated before, I still don't know what the conversion from UUID to VARBINARY does exactly, but it produces are binary data structure that then get implicitly cast to a numeric value when passed to the RAND() method. As a result, this seeds the RAND() method with a new value on each row and gives us the following output:
0.632351429673
0.277469709628
0.291371676631
0.187895386945
0.0332443201662
0.269721718406
0.801980914521
0.984558916944
0.569620907931
A random value for each record. Good stuff! Thanks a lot Josh!
Reader Comments
just in case you don't already know you can also use NEWID() to return random records from a table by using it in the ORDER BY clause:
ORDER BY NEWID()
you are they man. Great idea of of unique row values selection.
Thanks - this post helped me out :)
This is awesome - most of the other details I read were all about creating functions and views, which is cool if you actually have that kind of access to the database. This helped me de-identify some Personal Information data by randomly adding/subtracting somewhere between 1-365 days to the date of birth. Thanks! :)
@Chris,
Cool man, glad to help.
