

Grasshopper
Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21,
Visits: 69


Thanks Jeff.
That's my point. If something can generate both a negative and positive number 2, they may look random but abs reverses that effect.
Also, it seems to us that RAND is doing fine between rows since the seed being passed as proposed is changing.




SSCertifiable
Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 7,201,
Visits: 15,844


stan.teitelbaum (2/15/2010) Thanks Jeff.
That's my point. If something can generate both a negative and positive number 2, they may look random but abs reverses that effect.
Also, it seems to us that RAND is doing fine between rows since the seed being passed as proposed is changing.
I've actually used both. If you think about it, using RAND(Checksum(NewID())) will only generate as many unique values as Checksum(NewID()) in any given run since Checksum(NewID()) is what generates the "unique seed".
So it comes down to what kind of numbers do you need? Is it better to work from a float and muliply it out, or start with a big number and make it smaller to fit your actual allowable range. Since they're both basically the same, I tned to work with what is fastest to generate (i.e. Jeff's method listed above).
By the way  RAND() isn't random at all. For example, try running this:
select rand(1) go select rand(1) go select rand(1) go select rand(1) go select rand(1) go select rand(1) In other words  it generates a predictable result for the same seed, even on separate runs and on different sessions. The only thing that makes it "seem" random is that it will "pick" a seed during different runs if you don't pass it a seed.
It also apparently has something that only uses positive seeds , since
RAND(1) = RAND(1)
So again, you really don't get anything other that somewhat slower perf out of using RAND().
 Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loudspoken enduser..All right  what was my emergency again?




Grasshopper
Group: General Forum Members
Last Login: Monday, June 17, 2013 9:13 PM
Points: 21,
Visits: 69


thanks for clarifying Matt.




SSC Rookie
Group: General Forum Members
Last Login: Friday, February 28, 2014 4:15 PM
Points: 25,
Visits: 48


Thought I'd share ... use it as you see fit. I wrote this for something else I need to poc, etc.
Step 1:
CREATE VIEW dbo.vRand AS SELECT RAND() as [RANDOM]
Step 2:
USE SomeDB GO
/* ========================================================================================================================= Author: Goran Biljetina Create date: 20121113 Description: Random number generator between 1 and base^power LIMITS: Returns positive integers, base and exponent inputs are integers as well (that can be modified as needed) ========================================================================================================================= */ CREATE FUNCTION dbo.fnRandNumber ( @base bigint = 10 > base number, 10 by default ,@exp int > exponent value, e.g. max 18 when base = 10 ) RETURNS bigint AS BEGIN
  declare @r int, @ef int, @i int set @r= @exp+1 > range or maximum sequence number, how many multiplication loops? set @ef= 0 > initial multiplication factor set @i= 1 > increment of multiplication factor and decrement of range or sequence
declare @Power10 table (Number bigint, Sequence int); > generate sequence buckets based on base number and power value input begin while @r >= 0 begin insert into @Power10 (Number,Sequence) select power(@base,@ef),(@ef) set @r = (@r  @i) set @ef = (@ef + @i)
if @r <= 0 BREAK else CONTINUE end end  
> GENERATE RANDOM NUMBER FROM 1 to base^power (max exp=18 when base=10, 8 byte int limit) declare @rspan bigint, @rnumber bigint, @rseqmax int, @rseq int, @rseed bigint
set @rseqmax = @exp > get sequence or power limit set @rseq = (FLOOR(((select * from vRand)*@rseqmax))+1) > get random sequence w/in limit set @rseed = (select number from @Power10 where Sequence = @rseq) > get number seed w/in sequence
set @rspan = (FLOOR((((select * from vRand))*((select * from vRand)*@rseed)))+1) > get rand span w/in rand number seed set @rnumber = FLOOR((select * from vRand)*@rspan)+1 > get rand number w/in span
RETURN(@rnumber);
END GO
Step 3.
> SAMPLE SETS: select dbo.fnRandNumber(10,3)[(10,3)]; select dbo.fnRandNumber(10,5)[(10,5)]; select dbo.fnRandNumber(10,9)[(10,9)]; > max for base 10 (if return 8 byte signed integer) select dbo.fnRandNumber(10,18)[(10,18)]; select dbo.fnRandNumber(2,8)[(2,8)]; select dbo.fnRandNumber(2,16)[(2,16)]; select dbo.fnRandNumber(2,32)[(2,32)]; > max for base 2 (if return 8 byte signed integer) select dbo.fnRandNumber(2,62)[(2,62)];
And this, basically the same thing in JS:
// get random number within provided base + exponent // by Goran Biljetina // code as is :)
function isEmpty(value){ return (typeof value === "undefined"  value === null); } var numSeq = new Array(); function add(num,seq){ var toAdd = new Object(); toAdd.num = num; toAdd.seq = seq; numSeq[numSeq.length] = toAdd; } function fillNumSeq (num,seq){ var n; for(i=0;i<=seq;i++){ n = Math.pow(num,i); add(n,i); } } function getRandNum(base,exp){ if (isEmpty(base)){ console.log("Specify value for base parameter"); } if (isEmpty(exp)){ console.log("Specify value for exponent parameter"); } fillNumSeq(base,exp); var emax; var eseq; var nseed; var nspan; emax = (numSeq.length); eseq = Math.floor(Math.random()*emax)+1; nseed = numSeq[eseq].num; nspan = Math.floor((Math.random())*(Math.random()*nseed))+1; return Math.floor(Math.random()*nspan)+1; }
console.log(getRandNum(10,20),numSeq); //getRandNum(10,20); //console.log(getRandNum(10,20),numSeq); //console.log(numSeq);




Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:33 PM
Points: 9,
Visits: 0





Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 15, 2012 7:33 PM
Points: 9,
Visits: 0




