# question

asked

## 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?

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Great question.
0 Likes 0 ·

### 3 Answers

·

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

### Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.