Random Number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Random Number

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-15 : 10:38:41

Generates a true random number from xxx to zzz. Uses a table variable so it all depends on the boundaries you specify how long it will take.


create procedure random_number 
    @lower int = 0, 
    @upper int = 256,
    @number int = null output
as
    set nocount on

    declare @numbers table (number_id int identity(1,1), value int)

    if @lower > @upper 
        begin
            set @number = @lower
            set @lower = @upper
            set @upper = @number
        end

    set @number = rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate()))
    
    while (select count(*)
             from @numbers) < (@upper - @lower + 1)
        begin
            insert into @numbers (value)
                select (@upper - @lower + 1) * rand() + @lower
        end

    select @number = value
      from @numbers
     order by newid()
go

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-16 : 12:38:54

Er, maybe you could explain what this is doing, cause I've looked at it a few times and it still doesn't make sense to me.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-16 : 15:24:15

you mean comments?! :-p

first it creates a table with @n number of entries where @n = @upper - @lower + 1 ... each entry is a random number between @lower and @upper. next a random entry is selected from the table and returned.

the reason it had to be done this way is that the first call to RAND() usually happened so fast from a calling function (random_password) that it returned the same value since the time didn't really change. So this way it goes and selects a random spot from the table.

Edited by - onamuji on 04/16/2002 15:25:42

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-17 : 04:24:53

(Trying not to think about what happens when you want a number between 1 and 1000000...)
If you go to the other extreme, and consider the common case where it's selecting 0 or 1 -- flipping a virtual coin -- then there's still the problem of speed. Try this, for example:


DECLARE @r int, @i int

CREATE TABLE #RandTest (
  id int IDENTITY(1,1) PRIMARY KEY,
  r int
)

SET NOCOUNT ON

SET @i = 0
WHILE @i < 10000
BEGIN
  EXEC random_number 0, 1, @r OUTPUT
  --SET @r = FLOOR(RAND()*2.0)
  INSERT INTO #RandTest (r) VALUES (@r)
  SET @i = @i + 1
END

SET NOCOUNT OFF

select T1.r, T2.r, COUNT(*)
FROM #RandTest AS T1
INNER JOIN #RandTest AS T2 ON T1.id = T2.id - 1
GROUP BY T1.r, T2.r

DROP TABLE #RandTest

Creates a table of 10000 samples, then self joins it to compare each value with the next. Now since 0 and 1 are equally likely outcomes, the sequences 00, 01, 10 and 11 should be equally likely if the numbers are independent. However, I get significant skew towards the value staying the same for adjacent samples:


r1   r2   ct
0    0	  3588
1    1	  3698
1    0	  1356
0    1    1357

Compare this with what happens when the line using RAND() is uncommented instead of the call to random_number. This is still a noticeable effect when it's selecting from 0 to 9.

Edited by - Arnold Fribble on 04/17/2002 04:26:03

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-17 : 10:22:18

best result: xp_random_number ... write one... heh heh :-p

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-17 : 10:39:04

quote:

best result: xp_random_number ... write one... heh heh :-p

Eh?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 10:49:19

Don't know if this helps, but I tried this and it seems to give some decent results:

SELECT convert(varbinary, newID()) ^ 10000

You can use any bitwise operator, and change the 2nd operand to suit your tastes. If you use this, for example:

SELECT convert(varbinary, newID()) & 127

You'll get values between 0 and 127. The only problem with bitwise AND (&) is filling out the bit pattern properly.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-17 : 10:59:41

great solution ... great possibilities

Edited by - onamuji on 04/17/2002 11:07:49

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-17 : 11:17:07

so then a possibility being:


    set nocount on

    declare @compare int
    
    if @lower = @upper
        begin
            set @number = @lower
            return
        end

    if @lower > @upper 
        begin
            set @number = @lower
            set @lower = @upper
            set @upper = @number
        end

    - proper bit map
    set @compare = power(ceiling(log(@upper)/log(2)), 2) - 1
    set @number = convert(int, convert(varbinary, newid()) & @compare)

    -- until a valid number is returned
    while @number < @lower or @number > @upper
        begin
            set @number = convert(int, convert(varbinary, newid()) & @compare) + @lower
        end
go

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-17 : 11:23:01


create procedure random_number 
    @lower int = 0, 
    @upper int = 256,
    @number int = null output
as
    set nocount on

    declare @r float

    if @lower > @upper 
        begin
            set @number = @lower
            set @lower = @upper
            set @upper = @number
        end

    set @number = FLOOR(RAND(CONVERT(varbinary, NEWID()) & -1) *
                    (@upper-@lower+1)) + @lower
go

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-17 : 11:31:07

anything is possible ;-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 11:31:07

I added a little to the original, I'm sticking with bitwise OR (^):

SELECT (convert(varbinary, newid()) ^ 127) % 100

It will give you values from -99 to +99. You can add an offset to it, or use ABS() on it.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-17 : 11:45:45

Rob, this will pick 0 too frequently for small modulus values.
If you put the ABS around the convert it will fix most of the problem.
Why the ^ 127, though?

Edited by - Arnold Fribble on 04/17/2002 11:54:05

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 12:45:07

Yeah, it really seems to like those zeros

I only used 127 to fill out 7 bits with 1's. If I used 100, for example, I was getting a lot of even numbers and not too many odds. Try using 10; all I got was 0, 2, 8, and 10 as values. Actually, that was using bitwise AND instead of OR. It doesn't appear to matter as much using OR. You can use any number at all really.

It works much better with larger mod values (10,000+), which you could mod again with the upper limit:

SELECT ((convert(varbinary, newid()) ^ 127) % 1234567) % 10

I was really looking for a way to do it without needing any interative code; it's certainly random enough for me. I also tried this:

SELECT ((convert(varbinary, newid()) ^ convert(int, rand(9) * 100)) % 1234567) % 100

And it produces pretty nice distributions of all the possible values (on multiple runs) except for the zeros. I also tried it with a sequence table:

SELECT ((convert(varbinary, newid()) ^ convert(int, rand(seq) * 100)) % 1234567-seq) % 10
FROM Sequence WHERE seq<=100

And those damn zeros still show up!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-17 : 15:08:15

It's because % produces values in the range -n+1 ... n-1.
n%m for negative n will produce values in the range -m+1..0
n%m for non-negative n will produce values in the range 0..m-1
So the zeros show up twice.

quote:

Try using 10; all I got was 0, 2, 8, and 10 as values. Actually, that was using bitwise AND instead of OR.

Odd, that

php95saj
Starting Member

43 Posts

Posted - 2002-04-23 : 07:24:35

If I wanted to get a random number between 0 and 10000000, what would be the best way to do it. I try this:
CONVERT(INT, ((rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate())))* 100000000))

php95saj

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-23 : 07:57:34

You mean 0 and 99999999.
GETDATE is treated as a constant throughout a select, so this only works in situations where one value per select is required.
GETDATE can only change 300 times a second at most since that's the precision of datetime.
The initial value emitted by RAND correlates very strongly with the seed value.*

CONVERT(int, RAND(CONVERT(binary(4), NEWID())) * 100000000)

* If you plot n vs RAND(n), you'll find that you get a sawtooth wave with a period somewhere near 53668. e.g. (Numbers is a table of integers, as usual)


SELECT n, RAND(n) r
FROM Numbers
WHERE n BETWEEN 15373 AND 69040

Oh, marvellous! I've just noticed that
RAND(2147483563) to RAND(2147483647) and
RAND(-2147483563) to RAND(-2147483648) are all the same (and equal to RAND(0)).
And since the period of the wave doesn't divide the 32-bit space exactly, plugging a uniform-distributed 32-bit seed into RAND doesn't produce a uniform distributed float.

Edited by - Arnold Fribble on 04/23/2002 09:05:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources