Deterministic and Nondeterministic function in SQL Server

 

The terms deterministic and nondeterministic refer to the predictability of the results returned by functions, specifically when a function is called with the same set of inputs.

Deterministic Functions

A function is deterministic if it always returns the same result when called with the same input values, regardless of when or where it's called. Deterministic functions are predictable and do not depend on external factors like time or session state.

Examples of Deterministic Functions

Ø  Mathematical functions like ABS (), SQRT (), POWER (), etc.

Ø  String functions like UPPER (), LOWER (), LEN (), etc.

Ø  Logical functions like COALESCE()

Nondeterministic Functions

A function is nondeterministic if it may return different results even when called with the same input values. Nondeterministic functions depend on factors like the system time, session-specific data, or database state, which can vary between executions.

Examples of Nondeterministic Functions

Ø  GETDATE () or SYSDATETIME () - returns the current system date and time, which changes with each call.

Ø  NEWID () - generates a new unique identifier (GUID) each time it is called.

Ø  RAND () - generates a random number that can differ between calls.

Ø  System information functions like @@SPID (returns the current session ID)