What is a non-deterministic function?
A non-deterministic function is a function that can return different results for the same input.
Its result depends not just on its arguments, but also on external factors - time, database state, random values, and so on.
Examples of non-deterministic functions
sql
SELECT GETDATE(); -- the current time, changes every second
SELECT RAND(); -- a random number
SELECT NEWID(); -- a random unique identifierEven called with the same parameters, the result comes out different.
Why this matters
- Non-deterministic functions can't be cached or used in indexes.
- They're indispensable when you need a unique, random, or current-moment result, but unsuited to logic that requires stability.
Short Answer
Interview readyPremium
A concise answer to help you respond confidently on this topic during an interview.