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.

Non-deterministic to deterministic

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

Example

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.

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.

When a function is non-deterministic, there is some kind of randomness.

Documentation / Reference