SQLServerCentral.com / SQL Server 2005 / T-SQL (SS2K5) / Rand() function error / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 17:59:20 GMT20RE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxthank you[img]http://www.rlgf.info/16.jpg[/img][img]http://www.rlgf.info/13.jpg[/img][img]http://www.rlgf.info/14.jpg[/img][img]http://www.rlgf.info/17.jpg[/img][img]http://www.ryzu.info/10.jpg[/img]Thu, 15 Nov 2012 19:44:37 GMTstarshayizRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxThank you for your postsThu, 15 Nov 2012 19:44:33 GMTstarshayizRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxThought I'd share ... use it as you see fit.I wrote this for something else I need to poc, etc.Step 1:[code="sql"]CREATE VIEW dbo.vRandASSELECT RAND() as [RANDOM][/code]Step 2:[code="sql"]USE SomeDBGO/* =========================================================================================================================Author: Goran BiljetinaCreate date: 2012-11-13Description: Random number generator between 1 and base^powerLIMITS: Returns positive integers, base and exponent inputs are integers as well (that can be modified as needed)========================================================================================================================= */CREATE FUNCTION dbo.fnRandNumber( @base bigint = 10 --&gt; base number, 10 by default ,@exp int --&gt; exponent value, e.g. max 18 when base = 10)RETURNS bigintASBEGIN------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------declare @r int, @ef int, @i intset @r= @exp+1 --&gt; range or maximum sequence number, how many multiplication loops?set @ef= 0 --&gt; initial multiplication factorset @i= 1 --&gt; increment of multiplication factor and decrement of range or sequencedeclare @Power10 table (Number bigint, Sequence int); --&gt; generate sequence buckets based on base number and power value input begin while @r &gt;= 0 begin insert into @Power10 (Number,Sequence) select power(@base,@ef),(@ef) set @r = (@r - @i) set @ef = (@ef + @i) if @r &lt;= 0 BREAK else CONTINUE end end--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------&gt; 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 bigintset @rseqmax = @exp --&gt; get sequence or power limitset @rseq = (FLOOR(((select * from vRand)*@rseqmax))+1) --&gt; get random sequence w/in limitset @rseed = (select number from @Power10 where Sequence = @rseq) --&gt; get number seed w/in sequenceset @rspan = (FLOOR((((select * from vRand))*((select * from vRand)*@rseed)))+1) --&gt; get rand span w/in rand number seedset @rnumber = FLOOR((select * from vRand)*@rspan)+1 --&gt; get rand number w/in spanRETURN(@rnumber);ENDGO[/code]Step 3.[code="sql"]--&gt; SAMPLE SETS:select dbo.fnRandNumber(10,3)[(10,3)];select dbo.fnRandNumber(10,5)[(10,5)];select dbo.fnRandNumber(10,9)[(10,9)]; --&gt; 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)]; --&gt; max for base 2 (if return 8 byte signed integer)select dbo.fnRandNumber(2,62)[(2,62)];[/code]And this, basically the same thing in JS:[code="plain"]// 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&lt;=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);[/code]Thu, 15 Nov 2012 12:28:44 GMTgoran.biljetinaRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxthanks for clarifying Matt.Mon, 15 Feb 2010 14:57:22 GMTdb042188RE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspx[quote][b]stan.teitelbaum (2/15/2010)[/b][hr]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.[/quote]I've actually used both. If you think about it, using RAND(Checksum(NewID())) will only generate as many unique values as [b]Checksum(NewID())[/b] in any given run since [b]Checksum(NewID())[/b] 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:[code="sql"]select rand(1)goselect rand(1)goselect rand(1)goselect rand(1)goselect rand(1)goselect rand(1)[/code]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().Mon, 15 Feb 2010 14:52:40 GMTMatt Miller (#4)RE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxThanks 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.Mon, 15 Feb 2010 14:10:50 GMTdb042188RE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspx[quote][b]stan.teitelbaum (2/15/2010)[/b][hr]is this a better choice guys? RAND(checksum(newid())) ? perhaps because it increases randomness by not allowing dupes between negatives and positives? Perhaps also because none of us really knows if the combo of newid and checksim is as random as rand?[/quote]But it doesn't increase randomness... first, CHECKSUM doesn't do anything to prevent negative numbers (which is actually "more random" than just a positive set of numbers). Second, RAND in SQL server isn't random between rows in a set based result set... RAND requires a random seed for each row or you're relegated to a single row.As a side bar, it's documented in BOL that NEWID() will return a random GUID that also happens to be unique from a given server.Mon, 15 Feb 2010 14:01:46 GMTJeff ModenRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxis this a better choice guys? RAND(checksum(newid())) ? perhaps because it increases randomness by not allowing dupes between negatives and positives? Perhaps also because none of us really knows if the combo of newid and checksim is as random as rand?Mon, 15 Feb 2010 12:23:45 GMTdb042188RE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspx[quote][b]john.arnott (3/3/2009)[/b][hr]In that regard, the two functions both now behave the same in providing a consistent result when referenced multiple times.[/quote]Of course it will... they've used the same GUID 3 times for the same row... the following should make it crystal clear for any who are still in doubt...[code] SELECT GETDATE() AS [GETDATE() (Always Same)], RAND() AS [RAND() (Always Same)], NEWID() AS [NEWID() (NEVER Same)] FROM Master..spt_Values WHERE Type = 'P' AND Number &lt; 10[/code][quote]The article did not mention the difference between RAND and NEWID that you've pointed out. By comparison, here are two queries with sample output. In the first, both RAND and NEWID return new values for each reference. In the second, they behave differently, a distinction not made clear in BOL's "Behavior Change" table or in Ben-Gan's article. [font="Arial Black"]That non-intuitive difference is probably reason enough to shove RAND to the back of the shelf and always use NEWID.[/font][/quote]Heh... once converted to VARBINARY or some such, you can even used NEWID() as a seed for RAND if it's more intuitive. But, beware... Peter Larson, Matt Miller, and Michael Valentine Jones, I have all done some pretty good testing on it... the methods of using NEWID() with CHECKSUM are quite a bit faster than the VARBINARY conversion and RAND().I agree... knowing it's extreme limitations, I don't even bother with RAND() except in the rarest of cases.Tue, 03 Mar 2009 16:40:22 GMTJeff ModenRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspx[quote][b]Jeff Moden (3/2/2009)[/b][hrAh... but that's not true for NEWID() which will give you a different return for every row generated by a SELECT. The code I provided above is proof of that.[/quote]Thank you, Jeff. I went back to the Ben-Gan article and now realize that it addresses the behavior of RAND or NEWID when assigned in a table expression, such as a sub-query. In that regard, the two functions both now behave the same in providing a consistent result when referenced multiple times. This code gives you the same "rnd" three times and the same "nid" three times in SQL2005, but according to Ben-Gan, would give possibly different values for "rnd" and "nid" under SQL 2000. [code]select rnd, rnd, rnd, nid, nid, nid from (select rand() as rnd ,abs(checksum(newid()))%100 + 1 as nid ) as d;rnd rnd rnd nid nid nid---------------------- ---------------------- ---------------------- ----------- ----------- -----------0.455575000804869 0.455575000804869 0.455575000804869 54 54 54[/code]The article did not mention the difference between RAND and NEWID that you've pointed out. By comparison, here are two queries with sample output. In the first, both RAND and NEWID return new values for each reference. In the second, they behave differently, a distinction not made clear in BOL's "Behavior Change" table or in Ben-Gan's article. That non-intuitive difference is probably reason enough to shove RAND to the back of the shelf and always use NEWID.[code]select abs(checksum(newid()))%10 + 1 as nid1 ,abs(checksum(newid()))%10 + 1 as nid2 ,abs(checksum(newid()))%10 + 1 as nid3 ,abs(checksum(newid()))%10 + 1 as nid4 ,cast(rand()*10 as int)%10 + 1 as rnd1 ,cast(rand()*10 as int)%10 + 1 as rnd2 ,cast(rand()*10 as int)%10 + 1 as rnd3 ,cast(rand()*10 as int)%10 + 1 as rnd4nid1 nid2 nid3 nid4 rnd1 rnd2 rnd3 rnd4----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------8 10 6 4 2 3 10 7[/code][code]DECLARE @MinValue INT , @MaxValue INT , @Quantity INT SELECT @MinValue = 10, @MaxValue = 100, @Quantity = 10 SELECT TOP (@Quantity) cast(rand()*(@MaxValue-@MinValue+1) as int)%(@MaxValue-@MinValue+1)+@MinValue as ConstRand ,ABS(CHECKSUM(NEWID()))%(@MaxValue-@MinValue+1)+@MinValue as NotConstNewID FROM Master.sys.SysColumns sc1ConstRand NotConstNewID----------- -------------88 6488 5988 7688 1188 4188 3888 2688 6388 8988 53[/code]Tue, 03 Mar 2009 10:43:53 GMTjohn.arnottRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspx[quote][b]john.arnott (3/2/2009)[/b][hr]So what's wrong with RAND(), you may be wondering? As Jeff notes, the RAND (and also the NEWID) functions are deliberately designed to be invoked a single time in a call, even if referenced multiple times. In SQL 2000, each reference produced a different result as the function would be evaluated again for each reference. This was a deliberate design decision at Microsoft. An interesting discussion on this topic was posted by Itzik Ben-Gan at http://www.sqlmag.com/Article/ArticleID/97032/sql_server_97032.html[/quote]Ah... but that's not true for NEWID() which will give you a different return for every row generated by a SELECT. The code I provided above is proof of that.Mon, 02 Mar 2009 17:46:31 GMTJeff ModenRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxSo what's wrong with RAND(), you may be wondering? As Jeff notes, the RAND (and also the NEWID) functions are deliberately designed to be invoked a single time in a call, even if referenced multiple times. In SQL 2000, each reference produced a different result as the function would be evaluated again for each reference. This was a deliberate design decision at Microsoft. An interesting discussion on this topic was posted by Itzik Ben-Gan at http://www.sqlmag.com/Article/ArticleID/97032/sql_server_97032.htmlMon, 02 Mar 2009 14:21:03 GMTjohn.arnottRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspx[quote][b]mverma4you (3/2/2009)[/b][hr]you are not allowed to use the RAND() function inside UDF function. To call the value of this function create a view with the single statement select RAND()And then call that view inside your function. [/quote]And you still end up with a RBAR solution that way... Don't use RAND to generate random numbers... it just doesn't work the right way in T-SQL.Mon, 02 Mar 2009 05:34:02 GMTJeff ModenRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxyou are not allowed to use the RAND() function inside UDF function. To call the value of this function create a view with the single statement select RAND()And then call that view inside your function. Mon, 02 Mar 2009 04:52:31 GMTmverma4youRE: Rand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxThere is no need for the RBAR of a function to generate random integer values. The only reason you think so is because you found out that RAND returns the same number throughout a given single select. The following code produces 500 random integers from 10 to 100 across many rows in a single return as an example...[code]DECLARE @MinValue INT , @MaxValue INT , @Quantity INT SELECT @MinValue = 10, @MaxValue = 100, @Quantity = 500 SELECT TOP (@Quantity) ABS(CHECKSUM(NEWID()))%(@MaxValue-@MinValue+1)+@MinValue FROM Master.sys.SysColumns sc1 [/code]Sun, 01 Mar 2009 18:57:28 GMTJeff ModenRand() function errorhttp://www.sqlservercentral.com/Forums/Topic666309-338-1.aspxCREATE FUNCTION ufntGenerateRandValue (@minValue int,@maxValue int)RETURNS intASBEGINDECLARE @randNumber intSet @randNumber = Cast(RAND() * @maxValue + @minValue AS INT)RETURN @randNumber;ENDGOThis function compiles without error but when I tried to run it I got the following errors:Invalid use of side-effecting or time-dependent operator in 'rand' within a function.Please help me figure it outThank you.Sun, 01 Mar 2009 17:57:45 GMTmanoj005