Random Numbers without RAND

[mhoyt]

I started by reading vongrunts's excellent FAQ on random numbers. Highly recommended!

For my application, I need random integers 0 - 9 inclusive. The following code looks like it will work for me, it is lifted almost straight from vongrunt's FAQ.

A couple of things I ran into that might be of interest however. The first commented line yielded roughly twice as many 1's as I expected, and the second line never returned a zero. Solved by switching from LEFT to RIGHT and dropping the RAND function in favor of just using CHECKSUM against NEWID. But not really sure why this is so...?

USE SANDBOX

DECLARE @counter smallint
SET @counter = 1

DECLARE @TBLDICE TABLE (ROLLS INT)

WHILE @counter < 5000
   BEGIN
--  Next line tends to yield twice as many ones (1) as expected
--	INSERT INTO @TBLDICE SELECT LEFT(1 + 10*RAND(CHECKSUM(NEWID())),1)
--  Next Line never yields zero
--	INSERT INTO @TBLDICE SELECT RIGHT(1 + 10*RAND(CHECKSUM(NEWID())),1)
	INSERT INTO @TBLDICE SELECT RIGHT(CHECKSUM(NEWID()),1)
      SET NOCOUNT ON
      SET @counter = @counter + 1
      SET NOCOUNT OFF
   END


SELECT ROLLS, COUNT(ROLLS) FROM @TBLDICE GROUP BY ROLLS ORDER BY ROLLS

I'd be curious to see other people's approaches to random numbers. TIA Mike

 

[Gooser]

I like to use modulus (%) to do my random numbers, I guess I learned it somewhere once and just liked it.

USE NorthWind

DECLARE @RULE int --Range Upper Limit Extent --haha.

SET @RULE = 10

SELECT
--gives you a 0-thru-upper-limit (upper limit INclusive) list of pseudo-random integer values
(cast((rand(checksum(newid())) * datepart(ms, getdate())) AS int) % @RULE + 1 ) AS pseudoRandom1

--gives you a 0-thru-upper-limit (upper limit EXclusive) list of pseudo-random integer values
,(cast((rand(checksum(newid())) * datepart(ms, getdate())) AS int) % @RULE ) AS pseudoRandom2

--gives you a 1-thru-upper-limit (upper limit INclusive) list of pseudo-random integer values
,((cast((rand(checksum(newid())) * datepart(ms, getdate())) AS int) % @RULE - 1 ) + 2 ) AS pseudoRandom3

--gives you a 1-thru-upper-limit (upper limit EXclusive) list of pseudo-random integer values
,((cast((rand(checksum(newid())) * datepart(ms, getdate())) AS int) % ( @RULE - 1) ) + 1 ) AS pseudoRandom4

/******************************************
*
*  NOTE the use of the % (modulus) operator in these statements.
*
******************************************/

--Or, for a pair of dice, there is this
,((cast((rand(checksum(newid())) * datepart(ms, getdate())) AS int) % 6 ) + 1 ) AS dieOne
,((cast((rand(checksum(newid())) * datepart(ms, getdate())) AS int) % 6 ) + 1 ) AS dieTwo

--I just used Orders because it has 830 rows in it.
   FROM NorthWind.dbo.Orders

Hope you have fun with this!



v/r

Gooser

Why do today
that which may not need to be done tomorrow --me