July 28, 2008 at 10:29 am
I guess that I have to concede that the question has been correctly posed.
Nevertheless, it is tempting to think that functions should act in "isolation", and it kind of goes against my idea how a good function should work, that its expected behaviour could be changed by a previously defined instance of the same function. Any body feel the same?
July 28, 2008 at 10:44 am
jims posted code:
Set Nocount On
Create Table #Temp
(
Val int
)
Insert Into #Temp values (1)
Insert Into #Temp values (2)
Insert Into #Temp values (3)
Insert Into #Temp values (4)
Insert Into #Temp values (5)
Set Nocount Off
Select Rand() From #Temp
Drop table #Temp
...which returned the same value five times.
It strikes me that this is because a single RAND() was effectively joined to the five rows in the temp table and so simply was reported five times. The engine didn't evaluate RAND() five times, just once, then joined to each row in #Temp.
July 28, 2008 at 2:07 pm
john.arnott (7/28/2008)
jims posted code:
Set Nocount OnCreate Table #Temp
(
Val int
)
Insert Into #Temp values (1)
Insert Into #Temp values (2)
Insert Into #Temp values (3)
Insert Into #Temp values (4)
Insert Into #Temp values (5)
Set Nocount Off
Select Rand() From #Temp
Drop table #Temp
...which returned the same value five times.
It strikes me that this is because a single RAND() was effectively joined to the five rows in the temp table and so simply was reported five times. The engine didn't evaluate RAND() five times, just once, then joined to each row in #Temp.
Try the same thing with checksum(newid()) instead of rand().
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 3:45 pm
Ok maybe I'm just slow... 8 hours of training can do that... but...
If you can make a non-deterministic version of RAND() by feeding it a seed... why do I get the same basic error for the following two CREATE FUNCTION statements?
--Function 1
create function dbo.bob1(@input varchar)
returns float
AS
BEGIN
declare @val float
set @val = RAND()
RETURN @val
END
--Function 2
create function dbo.bob2(@input varchar)
returns float
AS
BEGIN
declare @val float
set @val = RAND(1)
RETURN @val
END
From this link http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx the error I get from trying to create either of the functions says that it's because it's non-deterministic, and function declarations hate that.
So It depends is the only valid answer, since providing a seed is supposed to make RAND calls deterministic, but doesn't in the case of functions?
July 29, 2008 at 4:16 am
GSquared (7/28/2008)
The question is correct. Once the seed value is supplied, it's deterministic. Till then, it's not. The quibble is over how the seed value is supplied, maybe, but the question and answer are still correct.
In your attempt to prove that the question is correct, you have in fact proven the 'it depends' school!
It goes as follows:
Look through a keyhole at the line of code 'SET @MyVar = RAND()'. Is the answer returned deterministic? I maintain 'it depends'. You maintain that it cannot be deterministic unless the seed parameter is supplied - somewhere else. You are relying on information that is not available to someone viewing that piece of code through the keyhole. The moment you start allowing SOMETHING outside the single line of code visible through the keyhole, you are allowing ANYTHING outside that keyhole view to be influential on the result of that line of code. That gives us the whole known (and unknown!) universe to play with - every electron, timing, and energy state known to be used in determining that initial, unseeded RAND() value. From such information, it is possible to determine what the initial value returned by RAND() will be, even without *ANY* seed parameter being supplied. That is to say, it is deterministic. You don't like my definition of deterministic? Well, as one of the options for the the Q says, "it depends".
Brewmanz
July 29, 2008 at 5:22 am
How about Select Rand(), NewId() from #Temp?
What happens?
Get single value for Rand
Get DIFFERENT Value for NewID
So in this scenario, Rand IS deterministic, else Sql would re-evaluate for each row, correct?
Yet no seed is passed in - so DON'T know what the particular value for Rand() is - So this makes it Non-Deterministic, correct?
Sure seems like "It Depends"
Why did I have this example? - Sql is supposed to perform Set based operations far better than non-set. I want a Random series of percentages for a given set of data (e.g. 0 - 1 for a couple thousand rows of information) - Naive implementation would be similar to above, select Rand() from WhateverTable - I was quite surprised to find the numbers to NOT be at all random, needed to play all sorts of games. (Particular scenario - Vary NbrValue between 90 and 115% of existing data. Found answer, but needed to pass a real random seed into Rand (Derived from a Guid), making Rand not very random, confused me a fair bit....)
Deterministic - Known value for known input (Seems to be no issue with this, if a particular seed is passed in, then output will always be the same)
Non Deterministic - UNABLE to determine the value with certainity - Key definition is "with certainity", and knowing "determine the value" - Determine a SINGLE value? There I would agree, only if a known seed is passed in. Determine a GROUP of values? - Maybe
July 29, 2008 at 5:34 am
Example of SQL ITSELF being unclear:
mtassin showed that it's NOT usable within a Function, even passing a static seed value - (Non Deterministic here)
The Select Rand(),NewID() showing SAME values for rand, yet different values for NewID - Shows Deterministic behavior here, else would be reevaluated per line like the NewID() is
So yes, it might be that the SQL Function creation team didn't allow for a static seed rand value, but at very least being able to show that Microsoft didn't get it entirely consistant helps show how difficult the Deterministic/Non-Deterministic behavior is
August 6, 2008 at 10:28 am
I thought that the question was a bit vague and would have been fairer (not necessarily any more accurate) as two questions; splitting up the seeded and non-seeded bits. Essentially this is an argument of how random a psudo-random number is - not about a point of Sql.
But to be honest with everyone I need to work out a lot of the questions by thinking about what is actually being asked, rather than what at first glance appears to be the question! I guess that there are so many clever people here that some tricks are employed to liven things up.
My query to get an answer was:
select rand(), rand(), rand()
select rand()
select rand()
select rand(1), rand(1), rand(1)
select rand(1)
select rand(1)
As all the seeded RANDs gave the same answer I figured that the question poser was calling this deterministic; pseudo-random number generation (as we all seem to know!) isn't very random, but I think random enough to derive the answer the questioner was looking for.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply