4

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.

No random numbers

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.

6

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
1

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
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.