May 19, 2014 at 7:55 pm
Hi
i did not find a satisfactory answer to my question over how can any function be non-deterministic? What I don't understand is how can a function like GETDATE() is non-deterministic? Sure the outputs will vary everyday but I mean the whole idea of function is to provide outputs based on inputs. i.e. AVG is deterministic and all aggregate functions are.(which is understandable.)
Any help will be appreciated. Currently I am studying towards 70-461.
May 19, 2014 at 8:36 pm
non-deterministic - "A property of a computation which may have more than one result."
So functions like NEWID() and GETDATE() are non-deterministic because they do not return the same result every time, as do functions like PI().
May 19, 2014 at 9:07 pm
Thank you chasing mary
May 20, 2014 at 3:54 am
bhavik.bk (5/19/2014)
Hii did not find a satisfactory answer to my question over how can any function be non-deterministic? What I don't understand is how can a function like GETDATE() is non-deterministic? Sure the outputs will vary everyday but I mean the whole idea of function is to provide outputs based on inputs. i.e. AVG is deterministic and all aggregate functions are.(which is understandable.)
Any help will be appreciated. Currently I am studying towards 70-461.
The key you are missing is that it has nothing (necessarily) to do with "outputs based on inputs". Many functions take no input and they may or may not be deterministic. As mentioned PI() takes no input but WILL return the exact same value every execution. GETDATE() takes no input but is NOT guaranteed to return the same value every execution (although it MAY do so if executed within 3.33 milliseconds of each other, which is the granularity of the datetime return type in SQL Server).
Likewise this function (pseudocode):
create function myDFunc (@a tinyint)
RETURNS int
RETURN @a * 2.5
Takes a parameter and WILL return the same value for every execution of the same @a input.
This function is NOT guaranteed to return the same value:
create function myNonDFunc (@a tinyint)
RETURNS float
RETURN @a * RAND()
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2014 at 3:47 pm
thanks kevin for clarification.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply