Deterministic and Nondeterministic Functions in SQL Server

Deterministic and nondeterministic functions are two fundamental concepts in SQL Server that determine how a function behaves when called with a specific set of input values.

Deterministic functions always return the same result every time they are called with a fixed set of input values and given the same state of the database. On the other hand, nondeterministic functions may return different results each time they are called with a fixed set of input values, even if the database state remains the same.

Understanding the determinism of functions is crucial for ensuring the reliability and performance of SQL Server queries. In this article, we will understand deterministic and non-deterministic functions in SQL Server.

Deterministic functions

Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database.

For example, the AVG() function always results in the same result, given the qualifications stated above.

Deterministic built-in functions

Here is the list of deterministic built-in functions in SQL Server.

The below functions are not always deterministic but could be deterministic when they are specified in a deterministic manner:

• CAST is deterministic until used with DateTime, smalldatetime, or sql_variant.
• ISDATE is deterministic only in case used with CONVERT function.
• CONVERT is deterministic until one of these conditions exists.
  1. Source type may be sql_variant.
  2. Target type may sql_variant & its source type is nondeterministic.

Nondeterministic functions

Nondeterministic functions result in different output each time they are called with a fixed set of input values even if the database state that they access remains the same.

For example, GETDATE() function, results the current date and time value, always a different value.

Built-in Function Determinism

You cannot impact the determinism of any built-in function. The built-in function might be deterministic or nondeterministic based on the property of the function, implemented by SQL Server. For example, using an ORDER BY clause in any query do not change the determinism of the function that is used in the query.

Nondeterministic built-in functions

Here is the list of nondeterministic built-in functions in SQL Server:

Conclusion

Deterministic and nondeterministic functions are essential concepts in SQL Server that determine how a function behaves when called with a specific set of input values. Understanding the determinism of functions is crucial for ensuring the reliability and performance of SQL Server queries