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


Deterministic Functions


Deterministic Functions

Author
Message
jims-723592
jims-723592
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 103
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
jims-723592
jims-723592
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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() 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
Melville
Melville
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 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.



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