Function - (Non) Deterministic - Predictable

1 - About

All functions are deterministic or non-deterministic:

  • Deterministic functions always return the same result any time they are called with a specific set of input values. If you give them the same set of instructions they always end up with the same result.
  • Non-deterministic functions may return different results each time they are called with a specific set of input values.

Whether a function is deterministic or non-deterministic is called the determinism of the function.

If for the same input, the result is unique, the function is deterministic, otherwise non-deterministic.

3 - Non-deterministic to deterministic

A Non-deterministic SQL function can be made deterministic by ordering the result set on a unique key.

4 - Example

4.1 - Deterministic

The DATEADD built-in function is because it always returns the same result for any given set of argument values for its three parameters.

4.2 - Nondeterministic

GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

5 - Documentation / Reference

code/function/deterministic.txt · Last modified: 2017/09/06 09:14 by gerardnico