|
Seeder question: I have read in BOL that some functions are deterministic, and some functions are non-deterministic? What does this mean and what is an example of each? Are there any functions that are both?
(comments are locked)
|
|
A deterministic function is one where you can be sure of the output based on input (in a particular database). So if you call the DAY function on a particular field, it will return you the numerical day for the date parameter passed in. DAY( '1-1-2001') always returns 1. A non-deterministic function is one that you can't predict the output. So RAND (with no seed) is a good example, but GETDATE(), @@CPU_BUSY, and similar functions that can't be predicted based on the input. Some functions can be either, depending on how they are used. Here's a good reference from Books Online: Deterministic and Nondeterministic Functions How did I guess you might have the best answer Steve? :)
Oct 16 '09 at 09:03 PM
Matt Whitfield ♦♦
(comments are locked)
|
|
A deterministic function will always return the same result given the same arguments, non-deterministic functions will vary each time they are called. For example a function that adds 1 to the argument passed in would always return 8, if the argument was 7 - a deterministic function. On the other hand, the GETDATE() Function returns a different value each time called, so it's non-deterministic.
(comments are locked)
|
|
A function can't be both, but GETDATE() is a function that will be treated as ok to use in user-defined functions because the system gets what GETDATE() does. It doesn't change with system settings, session options, etc. A function most certainly can be both - CONVERT being the most famous example...
Oct 16 '09 at 09:01 PM
Matt Whitfield ♦♦
Ok, but a function called in a certain way is either one or the other. Rand() with a seed is always deterministic. Without it, it never is.
Oct 16 '09 at 09:20 PM
Rob Farley
(comments are locked)
|
Great question.