0

I was trying to generate a random-return stored procedure.

Tried so many different ways, and then found a "Clean way" to do what I need..

But.. I am running some simulations, and the numbers of results in 0 and 1 is much less than other values.. What is not fair with this numbers...

This is my code:

DECLARE @val FLOAT

SET @val = (SELECT ROUND(RAND(CHECKSUM(NEWID())), 1))

INSERT INTO randomtable (value)
    SELECT @val
GO 1000000

These are the results:

Results of the simulations

As you can see, there's only 8800 results in 0 and 1 and the others have more than 17000 results..

How can I reliably create a system to generate random values?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
André Valvassori
  • 30
  • 4
  • Define "value". Do you want a whole number, a string like a varchar, or a fractional number or a character or something else? What is the range of the value or what is the length of the value if it is not a number? – Igor Jan 27 '20 at 18:41
  • i want a float, and the range is the specified in the image... 0 to 1, using 0.1,0.2,0.3... – André Valvassori Jan 27 '20 at 18:47
  • `select ABS(CHECKSUM(NewId())) % 11 * .1` – Igor Jan 27 '20 at 18:53
  • Does this answer your question? [How do I generate random number for each row in a TSQL Select?](https://stackoverflow.com/questions/1045138/how-do-i-generate-random-number-for-each-row-in-a-tsql-select) – Igor Jan 27 '20 at 18:55
  • Use the answer in the suggested duplicate but use 11 as the modules and then multiply by .1. Result: `SELECT ABS(CHECKSUM(NewId())) % 11 * .1` – Igor Jan 27 '20 at 18:56

1 Answers1

2

You could skip random():

select randomcol, count(*) as counter
from
(
select cast(ABS(CHECKSUM(NEWID())) % 11 /10.0 as decimal(2, 1)) as randomcol
from master.dbo.spt_values as a
cross join master.dbo.spt_values as b
) as a
group by randomcol
order by randomcol;
lptr
  • 1
  • 2
  • 6
  • 16