What is determinism?

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?

more ▼

asked Oct 16, 2009 at 07:05 PM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

Great question.
Oct 16, 2009 at 08:32 PM RBarryYoung
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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

more ▼

answered Oct 16, 2009 at 07:35 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

How did I guess you might have the best answer Steve? :)
Oct 16, 2009 at 09:03 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 16, 2009 at 07:57 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

A function most certainly can be both - CONVERT being the most famous example...
Oct 16, 2009 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, 2009 at 09:20 PM Rob Farley
(comments are locked)
10|1200 characters needed characters left

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.


more ▼

answered Oct 16, 2009 at 07:33 PM

Jay Bonk gravatar image

Jay Bonk
1.4k 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 16, 2009 at 07:05 PM

Seen: 5270 times

Last Updated: Oct 16, 2009 at 07:05 PM