Function - (Non) Deterministic - Predictable
Table of Contents
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.
2 - Articles Related
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.