make random numbers for each row and column

Question

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.

Answer 1

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;

The expression can be simplified if you need only 32-bit integers. Additional examples:

--positive int
SELECT ABS(CHECKSUM(NEWID()));
--positive and negative int
SELECT CHECKSUM(NEWID());
--positive bigint
SELECT CAST(SUBSTRING(CAST(NEWID() AS binary(16)),1, 7) AS bigint);
--negative and positive 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));

Answer 2

Try this:

select Rand1.n as RandomIntegerBetweenAB
     , Rand2.n as RandomIntegerBetweenXY
     , DateAdd(Day, Rand2.n, GetDate()) as RandomDateAddBetweenXY
     , Rand3.n as RandomInteger
from api.RandomIntBetween(0, 100) as Rand1
cross apply api.RandomIntBetween(-365, 365) as Rand2
cross apply api.RandomIntBetween(default, default) as Rand3;    

We use this view to generate a random integer (only because SQL Server won't simply let us use Crypt_Gen_Random() in a UDF):

create view api.vw_RandomInt
with schemabinding
as (
    select Cast(Crypt_Gen_Random(4) as int) as n
);

Using this view as a base we can get whatever random integer and then do some maths to rescale it:

create function api.RandomIntBetween (
    @x int = -2147483648
  , @y int = 2147483647
)
returns table
with schemabinding as
return (
    select Cast(Round(c.f, 0, 0) as int) as n
    from (values(
        case when @x < @y then @x else @y end
      , case when @y > @x then @y else @x end
    )) a ([Min], [Max])
    cross join api.vw_RandomInt as b
    cross apply api.NormalizeBetween(
        b.n
      , -2147483648
      , 2147483647
      , a.[Min] - 0.5
      , a.[Max] + 0.5
    ) as c
);

Where the definition of NormalizeBetween is:

create function api.NormalizeBetween (
    @f float = 1.
  , @x float = 0.
  , @y float = 1.
  , @a float = 0.
  , @b float = 1.
)
returns table
with schemabinding as
return (
    select ((@f - @x) * (@b - @a)) / NullIf(@y - @x, 0) + @a as f
);