# What is determinism?

 0 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 ♦♦ 29.4k ● 61 ● 65 ● 87 Great question. Oct 16, 2009 at 08:32 PM RBarryYoung add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 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 ♦♦ 5.1k ● 77 ● 79 ● 82 How did I guess you might have the best answer Steve? :) Oct 16, 2009 at 09:03 PM Matt Whitfield ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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.http://msdn.microsoft.com/en-us/library/ms178091.aspx more ▼ answered Oct 16, 2009 at 07:33 PM Jay Bonk 1.4k ● 2 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

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

Topics:

x344
x108
x58

asked: Oct 16, 2009 at 07:05 PM

Seen: 5113 times

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