Sql-server – make random numbers for each row and column

randomsql server

I will edit some fake data in my database. But if I will make random numbers for each row and column, it is not so random as I want it. The result can you see on image below.

Here is my code I use:

WITH x AS 
(
    SELECT mintwee, mineen, nul, pluseen, plustwee 
    FROM Topic
)
SELECT mintwee = CAST(RAND()*100 AS INT),
       mineen = CAST(RAND()*100 AS INT), 
       nul = CAST(RAND()*100 AS INT),
       pluseen = CAST(RAND()*100 AS INT),
       plustwee = CAST(RAND()*100 AS INT)
FROM x

and this:

WITH x AS 
(
    SELECT 
        mintwee = CAST(RAND()*100 AS INT), 
        mineen = CAST(RAND()*100 AS INT),
        nul = CAST(RAND()*100 AS INT), 
        pluseen = CAST(RAND()*100 AS INT), 
        plustwee = CAST(RAND()*100 AS INT)
    FROM Topic
)
SELECT mintwee, mineen, nul, pluseen, plustwee
FROM x ;

But both has the same result. I know, I must use an UPDATE statement, but this is only for try if the result is good. After it, I am going to use the UPDATE statement.

I use SQL server with an express 2014 version.

Best Answer

Since there is no correlation between the table rows and the RAND function, the expression is evaluated only once in the query.

One method to generated random values is with the NEWID() function. The example below generates random integer values between 0-99 range. The modulo value can be adjusted for a different range and you can use this basic technique to generate random values for dates, character, etc.

WITH x AS 
(
  SELECT mintwee, mineen, nul, pluseen, plustwee 
  FROM Topic
)
SELECT mintwee = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       mineen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       nul = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       pluseen = CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100,
       plustwee= CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 3) AS int) % 100
FROM x;

EDIT:

Per Aaron's comment, the expression can be simplified if you need only 32-bit integers. Additional examples:

--postive int
SELECT ABS(CHECKSUM(NEWID()));
--postive and negative int
SELECT CHECKSUM(NEWID());
--postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 7) AS bigint);
--negative and postive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 8) AS bigint);
--dates between now and 1 year ago
SELECT DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, GETDATE());
--random uppercase character
SELECT CAST(CAST(65 + ABS(CHECKSUM(NEWID()) % 26) AS binary(1)) AS char(1));

Related Solutions

Sql-server – How to assign different random values to each row in a SELECT statement

A subquery is evaluated once if possible. I can't recall what the "feature" is called (folding?) sorry.

The same applies to GETDATE and RAND functions. NEWID is evaluated row by row because it in intrinsically a random value and should never generate the same value twice.

The usual techniques are to use use NEWID as input to CHECKSUM or as a seed to RAND

For random values per row:

SELECT
   co1l, col2,
   ABS(CHECKSUM(NEWID())) AS Random1,
   RAND(CHECKSUM(NEWID())) AS Random2
FROM
   MyTable

If you want random order:

SELECT
   co1l, col2
FROM
   MyTable
ORDER BY
   NEWID()

If you want random order with a row order too. ActualOrder order here is preserved regardless of the order of the resultset

SELECT
   id, val,
   ROWNUMBER() OVER (ORDER BY id) AS id
FROM
   #t1
ORDER BY
   NEWID()

Edit:

In this case, we can state the requirement as:

return any random value from the set for each row in the set

the random value will be different from the actual value in any row

This is different to what I offered above which simply re-orders rows in various ways

So, I'd consider CROSS APPLY. The WHERE clause force row by row evaluation and avoids the "folding" issue and ensures that val and rnd are always different. CROSS APPLY can scale quite well too

SELECT
   id, val, R.rnd
FROM
   #t1 t1
   CROSS APPLY
   (SELECT TOP 1 val as rnd FROM #t1 t2 WHERE t1.val <> t2.val ORDER BY NEWID()) R
ORDER BY
   id

Sql-server – Unexpected results with random numbers and join types

By adding the additional SELECT it pushes the compute scalar evaluation deeper into the plan and gives the join predicate, the compute scalar at the top then references the earlier one.

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id

|--Compute Scalar(DEFINE:([Expr1071]=[Expr1070]))

|--Compute Scalar(DEFINE:([Expr1070]=(1)+abs(checksum(newid()))%(4)))

Still digging into just why it waits so late to do it, but currently reading this post by Paul White (https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html). Perhaps it has something to do with the fact that NEWID is not deterministic?

Best Answer

Related Solutions

Sql-server – How to assign different random values to each row in a SELECT statement

Sql-server – Unexpected results with random numbers and join types

Related Question