# 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;
``````

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));
``````
• `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. – Vladimir Baranov Oct 25 '15 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. – Dan Guzman Oct 25 '15 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
);
``````