Why is RAND() not producing random numbers?

问题:

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

回答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)

回答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)

回答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.

回答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