Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Deterministic Functions Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 29, 2008 5:22 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, May 22, 2014 5:50 AM Points: 90, Visits: 103
 How about Select Rand(), NewId() from #Temp?What happens?Get single value for RandGet DIFFERENT Value for NewIDSo 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
Post #542526
 Posted Tuesday, July 29, 2008 5:34 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, May 22, 2014 5:50 AM Points: 90, Visits: 103
 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() isSo 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
Post #542535
 Posted Wednesday, August 6, 2008 10:28 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, January 22, 2009 9:09 AM Points: 211, Visits: 18
 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.
Post #547706
 Posted Thursday, August 28, 2014 1:50 PM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 4:49 PM Points: 2,876, Visits: 235
 Thanks for the question.
Post #1608408

 Permissions