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

Solution 1

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)

Solution 2

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)

Solution 3

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.

Solution 4

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

View more solutions

Comments

[Stephen Turner]

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]

Perhaps the documentation should also say 'Repetitive calls of RAND() with similar seed values return similar results.'

[Oded]

@webturner - Not sure that is the case. And if it is, it is probably subject to change.

[Martin Smith]

@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.

[Stephen Turner]

Good point @MartinSmith while I've developed that into my own answer, if Elias edits his answer I'll remove my downvote.

[Doc]

Seems to work with: Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 8 2017 04:15:27

[Tim Schmelter]

Here is a way to generate random and different values for every row: stackoverflow.com/a/1214761/284240