March 1, 2009 at 5:57 pm
CREATE FUNCTION ufntGenerateRandValue
(
@minValue int,
@maxValue int
)
RETURNS int
AS
BEGIN
DECLARE @randNumber int
Set @randNumber = Cast(RAND() * @maxValue + @minValue AS INT)
RETURN @randNumber;
END
GO
This 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 out
Thank you.
March 1, 2009 at 6:57 pm
There 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...
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
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2009 at 4:52 am
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.
March 2, 2009 at 5:34 am
mverma4you (3/2/2009)
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.
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2009 at 2:21 pm
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
March 2, 2009 at 5:46 pm
john.arnott (3/2/2009)
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
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2009 at 10:43 am
Jeff Moden (3/2/2009)[hr
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.
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.
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
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.
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 rnd4
nid1 nid2 nid3 nid4 rnd1 rnd2 rnd3 rnd4
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
8 10 6 4 2 3 10 7
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 sc1
ConstRand NotConstNewID
----------- -------------
88 64
88 59
88 76
88 11
88 41
88 38
88 26
88 63
88 89
88 53
March 3, 2009 at 4:40 pm
john.arnott (3/3/2009)
In that regard, the two functions both now behave the same in providing a consistent result when referenced multiple times.
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...
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 < 10
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]
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2010 at 12:23 pm
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?
February 15, 2010 at 2:01 pm
stan.teitelbaum (2/15/2010)
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?
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2010 at 2:10 pm
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.
February 15, 2010 at 2:52 pm
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 loud-spoken end-user..All right - what was my emergency again?
February 15, 2010 at 2:57 pm
thanks for clarifying Matt.
November 15, 2012 at 12:28 pm
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: 2012-11-13
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);
November 15, 2012 at 7:44 pm
Thank you for your posts
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply