Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Deterministic Functions Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2008 5:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 7:35 AM
Points: 90, Visits: 102
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
Post #542526
Posted Tuesday, July 29, 2008 5:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 7:35 AM
Points: 90, Visits: 102
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

Post #542535
Posted Wednesday, August 06, 2008 10:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse