Interview Question of the Week #053 – What is the Difference Between Deterministic Functions and Nondeterministic Functions?

Question: What distinguishes deterministic and nondeterministic functions? A deterministic function returns the same result for the same inputs and the same database state. A nondeterministic function may return a different result even when those remain the same.

My original reaction was that this sounded like a theoretical interview question. Knowing the definition alone does not diagnose a slow query. It becomes useful when the answer explains what SQL Server can persist or index.

SELECT ABS(-7) AS AbsoluteValue,
       DATEDIFF(day,CONVERT(date,'20160101',112),CONVERT(date,'20160105',112)) AS Days,
       ISNULL(CONVERT(int,NULL),10) AS ReplacedNull;
SELECT NEWID() AS NewIdentifier,GETDATE() AS CurrentServerTime;
SELECT RAND(123) AS Seeded1,RAND(123) AS Seeded2;
-- Inspect a function that actually exists in the current database:
-- SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.MyFunction'),'IsDeterministic');

The first result is 7, 4, 10. ABS, DATEDIFF and ISNULL are deterministic built-ins. NEWID, GETDATE and the original example @@CPU_BUSY are nondeterministic. That classification does not promise that two immediately adjacent calls must visibly differ.

Some functions depend on how you call them

The original list put RAND in the nondeterministic category without a qualification. RAND with an explicit seed is deterministic; RAND without one is not. The two seeded calls above produce the same value. Certain date conversions also depend on the supplied conversion style.

Deterministic is one indexing requirement

An indexed computed expression or indexed view must meet determinism requirements, along with other rules such as precision, data types, ownership and required SET options. Deterministic does not mean that an arbitrary expression is automatically indexable.

A user-defined function needs SCHEMABINDING to be deterministic, but that clause alone does not turn nondeterministic operations inside it into deterministic ones. OBJECTPROPERTY can report IsDeterministic for an existing visible function; NULL can mean a missing object or unavailable metadata.

Ordinary T-SQL functions cannot modify application database state. Functions calling legacy extended procedures are a separate documented source of nondeterminism and possible external side effects.

For a report, capturing GETDATE once gives its steps one consistent reference time. Repeating the report tomorrow still captures tomorrow’s time. Persist the intended reference value and control the data snapshot if reproducibility across runs matters.

So my follow-up question remains: where has this distinction changed a real design decision for you? An example involving an indexed expression is more useful than memorizing the list.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.