SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deterministic Functions


Deterministic Functions

Author
Message
Steven Cameron
Steven Cameron
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2237 Visits: 215
Ben Leighton (7/28/2008)
Am in there with the nay-sayers... For a single connection... RANd() behaves deterministically if following a seeded definition. Give us our points!


Since all subsequent calls to RAND use the same seed parameter that was specified (or randomize) in the first call, then calling RAND() without a parameter still has the seed parameter specified. So the answer is Yes.
GSquared
GSquared
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73283 Visits: 9730
Hate to say it, but read your own arguments about it not being right. Every single one of them says, "once a seed value has been set".

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.

- 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
jim.powers
jim.powers
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3238 Visits: 874
Based purely on the text of the question and the text in the link, the correct answer IS yes.

Interesting find here, when I run a SELECT RAND() on my servers, I get the exact same result on different boxes with different connections and different versions. It would appear that RAND() does not get a random seed from SQL Server (or maybe something is not setup correctly?) so it would be best to properly seed it every time it is used.
Jamie-2229
Jamie-2229
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4192 Visits: 861
From WIKI -
This causal determinism has a direct relationship with predictability. (Perfect) predictability implies strict determinism, but lack of predictability does not necessarily imply lack of determinism. Limitations on predictability could alternatively be caused by factors such as a lack of information or excessive complexity. An example of this could be found by looking at a bomb dropping from the air. Through mathematics, we can predict the time the bomb will take to reach the ground, and we also know what will happen once the bomb explodes. Any small errors in prediction might arise from our not measuring some factors, such as puffs of wind or variations in air temperature along the bomb's path.


Take it from another perspective:

For example, say we observe cooled molecules from an air conditioning unit in a large mall perhaps floating from a large duct into a room. For this environment create an equation (a quadratic: A(i)2 + 2A(i)B(i) + B(i)2 - I'm not an A/C specialist - this is probably not the correct equation) such that the imaginary number mirrors our environment at a specific moment of time where A may be the initial temperature and B, the speed of travel at the vent source. While we cannot mimic the dimension of time, we can, however; using the imaginary number as a mirror, determine what may happen in a fixed environment as a given time in a given space. This allows the A/C designer to create an efficient system in that mall for a given set of variables.

Thus the point of the RAND function... one grabs a random sample from a "fixed" set of data which will always be unique, but the uniqueness is based on a fixed dataset (try the example from the first post on your largest database and run it a few times). Technically random, but never non-deterministic.

Jamie
Steve Jones
Steve Jones
SSC Guru
SSC Guru (177K reputation)SSC Guru (177K reputation)SSC Guru (177K reputation)SSC Guru (177K reputation)SSC Guru (177K reputation)SSC Guru (177K reputation)SSC Guru (177K reputation)SSC Guru (177K reputation)

Group: Administrators
Points: 177968 Visits: 19497
I might agree the question is vaguely worded, but arguing that once you supply a seed you don't have to supply one the second time seems nitpicking.


Also, getting multiple records from the call means that the call is made once, not that it's a deterministic value.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ben Leighton
Ben Leighton
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 359
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?
john.arnott
john.arnott
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4262 Visits: 3059
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.
GSquared
GSquared
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73283 Visits: 9730
john.arnott (7/28/2008)
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.



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
mtassin
mtassin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8406 Visits: 72521
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?



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
brewmanz
brewmanz
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 406
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search