August 7, 2006 at 10:28 am
In testing the following random number generating code, I determined that the results are not entirely random.
round
(((upperBound - lowerBound) * rand() + lowerBound), 0)
Here's my testing code:
declare
@temp table(number int)
declare @i int
set @i = 0
while @i < 5000
begin
insert into @temp
-- Generate number from 1 to 50
select round(((50 - 1) * rand() + 1), 0)
set @i = @i + 1
end
select number, count(number) occurrences
from @temp
group by number
order by number
The above test will (should) generate a random integer from 1 to 50. I've noticed that the outer bounds are only generated half as often as the rest of the range. The test continuously proves this point with all sample sizes (that are large enough to make the slight skews noticeable-- 5000 or greater). Anyone have any ideas as to why the code/SQL Server is exhibiting this behavior.
Thanks for your help!
Jay
August 10, 2006 at 8:00 am
This was removed by the editor as SPAM
August 18, 2006 at 2:28 pm
An interesting question.
It would appear that the use of the Round function is really at the heart of it. Let me give you an example.
You want a number between 10 and 20. You generate a random non integer number between 10 and 20. Looking at the numbers you will notice that
10 - 10.4999 rounds to 10,
10.5 to 11.4999 rounds to 11
11.5 to 12.4999 rounds to 12
etc.
19.5 - 20 rounds to 20
Once you look at this way, it is easy to see why 10 and 20 will recieve 1/2 as many hits as all the other numbers.
August 21, 2006 at 11:09 am
Intriguing.
Thanks for your insight. I ended up doing things a little differently:
Rather than generating a number and matching that to a serial number for a particular record, I decided to simply select a random record through the use of the following code:
SELECT TOP 1 *
FROM Table
ORDER BY NewID()
Piece of cake! I tested it too, and it behaves as expected for a random selection.
Thanks for the explanation!
Jason
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply