make random numbers for each row and column

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.

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));
``````
• `NEWID()` was never meant to be a random number generator, so it is not clear what distribution you'll get for random numbers generated this way. Since SQL Server 2008 there is `CRYPT_GEN_RANDOM` that may be easier to use (you can specify the length of the generated number in bytes) and which produces "better" (cryptographically) random numbers. Commented Oct 25, 2015 at 10:14
• @VladimirBaranov, I completely agree `NEWID` isn't adequate for cryptography but that is not the use case here. `CRYPT_GEM_RANDOM` is a good idea to use here though, since it eliminates the `SUBSTRING` ugliness. Commented Oct 25, 2015 at 11:34

Try this:

``````select Rand1.n as RandomIntegerBetweenAB
, Rand2.n as RandomIntegerBetweenXY
, 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
);
``````