Idempotence vs. Determinism

Two important concepts when it comes to writing functions in Access are idempotence and determinism.

Idempotent functions can safely be run multiple times.  For example, an idempotent function that inserts records into a table is one that would avoid creating duplicate records even if you run it repeatedly.

Deterministic functions always return the same outputs given the same inputs.  The easiest way to understand this concept is with a counterexample.  The VBA Now() function is nondeterministic because it returns a different result every time you run it (the current date/time).  Nondeterministic functions are difficult to test and verify because their results are, by definition, not reproducible.

For more information on these two important concepts, let's consult StackOverflow and ChatGPT.

StackOverflow

Here's an excerpt from Dai's excellent answer to the question about determinism and idempotence:

• Pure deterministic function: The output is based entirely, and only, on the input values and nothing else: there is no other (hidden) input or state that it relies on to generate its output. There are no side-effects or other output.
• Impure deterministic function: As with a deterministic function that is a pure function: the output is based entirely, and only, on the input values and nothing else: there is no other (hidden) input or state that it relies on to generate its output - however there is other output (side-effects).
• Idempotency: The practical definition is that you can safely call the same function multiple times without fear of negative side-effects. More formally: there are no changes of state between subsequent identical calls.

Idempotency does not imply determinacy (as a function can alter state on the first call while being idempotent on subsequent calls), but all pure deterministic functions are inherently idempotent (as there is no internal state to persist between calls). Impure deterministic functions are not necessarily idempotent.

Be sure to read the full answer for additional context and a handy matrix that further clarifies the pure vs. impure distinction.

What is Idempotence?

[MIKE]: What is idempotence as it relates to programming?

[ChatGPT]: In programming, idempotence refers to the property of an operation to have the same result no matter how many times it is run, provided that the inputs and any relevant external state remains the same. Essentially, an idempotent operation should produce the same output every time it is executed, and have no side effects or unintended consequences from repeated execution. This property is useful in certain situations, such as in HTTP requests, where the same request may be sent multiple times and should result in the same outcome.

What is Determinism?

[MIKE]: What is deterministic as it relates to programming?

[ChatGPT]: In programming, deterministic refers to the property of an algorithm or operation to always produce the same output, given the same input and initial state. A deterministic function will always return the same result for the same set of inputs, making it predictable and easy to test. This is in contrast to non-deterministic algorithms, which can produce different outputs for the same input and state, making them more difficult to test and debug. Deterministic algorithms are important in many applications, as they provide consistency and reliability in the results they produce.

What are the Key Differences?

[MIKE]: What are the key differences between a function that is deterministic versus one that is idempotent?

[ChatGPT]: A deterministic function is a function that always returns the same output for the same input, whereas an idempotent function is a function that, when repeated multiple times, has the same effect as being executed only once.

The key difference between these two properties lies in the context of their application. A deterministic function will always produce the same output for the same input, regardless of how many times it is executed. On the other hand, an idempotent function is designed to produce the same output even when it is executed multiple times.

In other words, idempotence deals with the behavior of a function when executed multiple times, whereas determinism deals with the behavior of a function when executed once with the same input.

Therefore, a function can be deterministic without being idempotent, and vice versa. A function can also be both deterministic and idempotent, if it produces the same output every time it is executed and the same output is produced regardless of the number of times it is executed.

Acknowledgements

• Portions of this article's body generated with the help of ChatGPT

Cover image created with Microsoft Designer