Why doesn't RAND () produce random numbers?

Question

I answered a question and ran my SSMS to check out a small request before sending it, but got some weird results. Here's the request:

UPDATE Person SET Pos_X = Rand() , Pos_Y = Rand(id) SELECT ID, Surname, Forename, Pos_X, Pos_Y FROM Person 

And here is the result:

 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 

Since I expected Rand without a seed to put the same result on every line, but I was hoping that a rand with a seed (albiet only numbers 1 through 9) would be slightly better than an ordered list within 0,0002 of each other

Do you get the same? It sounds like something that can catch the careless.

As I am 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) ' 

Answer 1

RAND (Transact SQL) :

Returns a pseudo random floating point value from 0 to 1, excluding.

and

Repeated calls to RAND () with the same seed value return the same results .

(my accent)

Answer 2

Could you try to execute the query below, which always provides the desired random number.

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

Update . In conversation, I changed my answer as shown below:

 SELECT CAST(CRYPT_GEN_RANDOM(4) AS INT) 

Answer 3

The CRYPT_GEN_RANDOM function in Elias's answer made me work 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 creates the same float between 0 and 1 as RAND (), so it can fall where the seed is not used. It will also be called based on each row, as shown by the pattern. However, unlike RAND using the same seed, it does not produce the same result.

---

Note

This doesn't work anymore:

 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) 

but

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

Answer 4

to ensure that rand () is called separately for each row, do the following:

 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