Deterministic vs Nondeterministic Functions

Deterministic vs Nondeterministic Functions in SQL

In SQL, functions are categorized as deterministic or nondeterministic based on whether they produce consistent results under the same conditions.

Deterministic Functions

Deterministic functions always return the same result when called with the same input values and database state.

• ABS(-5): Always returns 5.
• ROUND(123.456, 2): Always returns 123.46.
• DATEADD(day, 5, '2025-04-15'): Always adds 5 days to the given date.

Nondeterministic Functions

Nondeterministic functions may return different results even when called with the same input values, depending on factors like system state or time.

• GETDATE(): Returns the current date and time, which changes with each call.
• NEWID(): Generates a unique identifier (GUID) that is different every time.
• RAND(): Produces a random number, which varies with each execution.

Key Differences

Here are the main distinctions between deterministic and nondeterministic functions:

• Deterministic Functions: Predictable and can be used in indexed views or computed columns.
• Nondeterministic Functions: Not predictable and cannot be used in indexed views.

Determining if a function is deterministic or nondeterministic

That can be check whether a function is deterministic by querying the is_deterministic object property for the function. The example below determines if the function dbo.NosToWord is deterministic.:

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.NosToWord'), 'IsDeterministic') AS IsDeterministic;

The result will be 1 if the function is deterministic and 0 if it is nondeterministic.

Built-in function determinism

All of the string built-in functions are deterministic, except for FORMAT. The following built-in functions from categories of built-in functions other than string functions are always deterministic.

CAST CONVERT PARSE TRY_PARSE TRY_CONVERT TRY_CAST

ABS ACOS ASIN ATAN ATN2 CEILING

COALESCE COS COT DATALENGTH DATEADD DATEDIFF

DAY DEGREES EXP FLOOR ISNULL ISNUMERIC

LOG LOG10 MONTH NULLIF POWER RADIANS

ROUND SIGN SIN SQRT SQUARE TAN

The following built-in functions are nondeterministic:

• CURRENT_TIMESTAMP
• NEWID
• RAND
• SYSUTCDATETIME
• SYSUTCOFFSET