update multiple rows with random 9 digit number using rand() function

Question

I am trying to update multiple rows with random 9 digit number using the following code.

UPDATE SGT_EMPLOYER 
SET SSN = (CONVERT(NUMERIC(10,0),RAND()  * 899999999) + 100000000)
WHERE EMPLOYER_ACCOUNT_ID = 123456789;

Expected result: the query should update 300 rows with 300 random 9 digit numbers.

Actual: query is updating 300 rows with same number as the ran() function is executing only once.

Please help. Thank You.

Answer 1

As you already figured out yourself, RAND is a run-time constant function in SQL Server. It means that it is called once per statement and the generated value is used for each affected row.

There are other functions that are called for each row. Often people use NEWID usually together with CHECKSUM as a substitute for a random number, but I would not recommend it because the distribution of such random numbers is likely to be poor.

There is a good function specifically designed to generate random numbers: CRYPT_GEN_RANDOM. It is available since at least SQL Server 2008.

It generates a given number of random bytes.

In your case it would be convenient to have a random number as a float value in the range of [0;1], same as the value returned by RAND.

So, CRYPT_GEN_RANDOM(4) generates 4 random bytes as varbinary. Convert them to int, divide by the maximum value of 32-bit integer (4294967295) and add 0.5 to shift the range from [-0.5;+0.5] to [0;1]:

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

Your query becomes:

UPDATE SGT_EMPLOYER 
SET SSN = 
    CONVERT(NUMERIC(10,0),
    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 899999999.0 + 100000000.0)
WHERE EMPLOYER_ACCOUNT_ID = 123456789;

Answer 2

Yes, the rand() line will only be executed once, before the rows are being updated, not every time a row is updated.

You can use a Stored Procedure to update every row with (CONVERT(NUMERIC(10,0),RAND() * 899999999) + 100000000).