SQL SERVER – Deterministic and Non Deterministic Functions

A function is said to be Deterministic when it returns the same result any time with the same set of input values.

A function is said to be Non Deterministic when it returns the different results each time with the same set of input values.

Let us take an user defined function which takes two integers values as input parameters and returns their sum. In this case, if the input parameters are 2 and 3 then each time this function will return the same result 5 which is deterministic.

To see this through an example, Let us first create a function ADD_NUMBERS which has two input parameters Num1 and Num2 and returns their sum.

CREATE FUNCTION ADD_NUMBERS(Num1 INT,Num2 INT) RETURNS INT
AS RETURN (NUM1 + NUM2)

Now, whenever we execute this function with the same input values 2 & 3 it returns 5.

Similarly the DATEDIFF system function always returns the same result for any given set of argument values for its three parameters.

As shown in the below query, DATEDIFF function always returns 5 for the same set of input values.

All of the Aggregate and String Built-in functions are deterministic except the CHARINDEX and PATINDEX.

Again, GETDATE() is the best example for  Non Deterministic Function because each time it returns different values with the same input parameter. As shown in the below screen shot, GETDATE() function returns different time values.

All of the configuration, cursor, meta data, security, and system statistical functions are nondeterministic.Some more examples for Non deterministic functions are : PATINDEX,CHARINDEX, CURRENT_TIMESTAMP,@@ERROR.