What is a deterministic function?
A deterministic function is a function that always returns the same result for the same input.
It doesn't depend on external factors, such as the current time, random numbers, or database state.
Example of a deterministic function
sql
CREATE FUNCTION add_ten(x INT)
RETURNS INT
AS
BEGIN
RETURN x + 10;
END;Calling add_ten(5) always returns 15.
And here's an example of a non-deterministic function:
sql
SELECT RAND(); -- a random number
SELECT GETDATE(); -- the current timeEvery call gives a different result, even with the same arguments.
Why this matters
- Deterministic functions can be cached and optimized.
- Non-deterministic ones can't, since the result is unpredictable.
Short Answer
Interview readyPremium
A concise answer to help you respond confidently on this topic during an interview.