Why is RAND() not producing random numbers?

Question

I was answering a question hereabouts and fired up my SSMS to test a little query before posting it, but got some strange results. Here's the query:

UPDATE Person
SET   Pos_X = Rand()
    , Pos_Y = Rand(id)

SELECT ID, Surname, Forename, Pos_X, Pos_Y FROM Person

And here are is the result set:

1   Bloggs  Fred    0.332720913214171   0.713591993212924
2   Doe     Jane    0.332720913214171   0.713610626184182
3   Smith   Mary    0.332720913214171   0.71362925915544
4   Jones   Martha  0.332720913214171   0.713647892126698
5   Jones   Martha  0.332720913214171   0.713666525097956
6   Jones   Martha  0.332720913214171   0.713685158069215
7   Jones   Martha  0.332720913214171   0.713703791040473
8   Jones   Martha  0.332720913214171   0.713722424011731
9   Jones   Martha  0.332720913214171   0.713741056982989

As I expected Rand without a seed put the same result in each row, but I was hoping that rand with a seed (albiet just the numbers 1 to 9) would do a bit better than an ordered list within 0.0002 of each other!

Do you get the same? This sounds like something that could catch out the unwary.

As I'm sure this will be relevant:

@@Version = 'Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)   Dec 10 2010 10:56:29   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1)  '

Rand will always generate the same results if the same seed is supplied. We use SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) to generate random ints. — dash, Dec 04 '11 at 21:11
Oh, and http://stackoverflow.com/questions/1214748/rand-not-different-for-every-row-in-t-sql-update seems to present an even more elegant way. — dash, Dec 04 '11 at 21:17

score 15 · Accepted Answer · answered Dec 04 '11 at 21:09

15

RAND (Transact SQL):

Returns a pseudo-random float value from 0 through 1, exclusive.

And:

Repetitive calls of RAND() with the same seed value return the same results.

(emphasis mine)

answered Dec 04 '11 at 21:09

Oded

489,969
99
883
1,009

2

Perhaps the documentation should also say 'Repetitive calls of RAND() with similar seed values return similar results.' – Stephen Turner Dec 05 '11 at 11:43
@webturner - Not sure that is the case. And if it is, it is probably subject to change. – Oded Dec 05 '11 at 11:44
Here is a way to generate random and different values for every row: https://stackoverflow.com/a/1214761/284240 – Tim Schmelter Oct 17 '17 at 10:07

Elias Hossain · Answer 2 · 2011-12-05T17:11:24.637

6

Would you please try with below query, which always provides desired random number.

SELECT RAND(CAST(RIGHT(CAST(CAST(CRYPT_GEN_RANDOM(4) 
                         AS INT) AS VARCHAR(100)), 1) AS INT))

Update: According to conversation, I've changed my answer as below:

SELECT CAST(CRYPT_GEN_RANDOM(4) AS INT)

edited Dec 05 '11 at 17:11

answered Dec 05 '11 at 07:41

Elias Hossain

4,410
1
19
33

I hope you aren't relying on this too much, CRYPT_GEN_RANDOM only returns a one byte seed value, I tested this with 10 rows and got most rows with 0.7136 +/- 0.001 – Stephen Turner Dec 05 '11 at 11:39
2

@webturner - You can just use `select CAST(CRYPT_GEN_RANDOM(4) AS int)` to get a random int if you are on SS2008. No need to involve `rand` at all. – Martin Smith Dec 05 '11 at 11:54
1

Good point @MartinSmith while I've developed that into my own answer, if Elias edits his answer I'll remove my downvote. – Stephen Turner Dec 05 '11 at 15:24

Stephen Turner · Answer 3 · 2016-08-05T16:01:24.123

The CRYPT_GEN_RANDOM function in Elias's answer, got me working on a better solution:

CREATE FUNCTION dbo.MyRAND(@Seed as bigint) RETURNS float(53) AS
BEGIN
    --Sample: SELECT dbo.MyRAND(DEFAULT), dbo.MyRAND(DEFAULT), dbo.MyRAND(12345) FROM ( SELECT 1 AS ID UNION SELECT 2 UNION SELECT 3 ) as ThreeRows
    DECLARE @Return as float(53)
    IF @Seed = 0
        SET @Return = (Cast(CRYPT_GEN_RANDOM(8) as bigint) + POWER(Cast(2 as float(53)), 63)) / POWER(2.0, 64)
    ELSE 
        SET @Return = (Cast(CRYPT_GEN_RANDOM(8, CAST(@Seed AS varbinary(8))) as bigint) + POWER(Cast(2 as float(53)), 63)) / POWER(2.0, 64)
    RETURN @Return
END

This produces the same float between 0 and 1 as RAND() should, so could drop in where a seed isn't used. It will also be called on a per row basis as demonstrated by the sample. However unlike RAND using the same seed doesn't produce the same result.

Note

This no longer works in:

Select @@Version 
    Microsoft SQL Server 2014 - 12.0.4100.1 (Intel X86) 
    Apr 20 2015 17:34:37 
    Copyright (c) Microsoft Corporation
    Developer Edition on Windows NT 6.1 <X64> (Build 7601: ) (WOW64)

as

Msg 443, Level 16, State 1, Procedure MyRAND, Line 10
Invalid use of a side-effecting operator 'Crypt_Gen_Random' within a function.

Seems to work with: **Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 8 2017 04:15:27** — Doc, Mar 22 '17 at 14:20

score 1 · Answer 4 · edited Dec 05 '11 at 11:51

to make sure that rand() is invoked separately per each row, do this:

create view wrapped_rand_view
as
    select rand( ) as random_value
go

create function wrapped_rand()
returns float as
begin
    declare @f float
    set @f = (select random_value from wrapped_rand_view)
    return @f
end

select 
    ThreeRows.ID, dbo.wrapped_rand() wrapped_rand
from 
    ( SELECT 1 AS ID UNION SELECT 2 UNION SELECT 3 ) as ThreeRows

Why is RAND() not producing random numbers?

4 Answers4

Linked

Related