SQL Server: Deterministic and Non-Deterministic Function

A function is deterministic if it returns the same value each time it is passed the same values. Nondeterministic user-defined functions have certain restrictions on how they can be used. In this scenario, the best approach would be to rewrite the function as a stored procedure because stored procedures do not have this restriction. If DBA/Developer are not sure whether a function is deterministic, DBA/Developer can return the IsDeterministic property using the OBJECTPROPERTY function. The IsDeterministic property is 1 if the function is deterministic, and 0 if it is not. For example, DBA/Developer could use the following query to determine whether the dbo. udf_get_days_empfunction is deterministic or nondeterministic:

SELECT OBJECTPROPERTY

(OBJECT_ID ('dbo.udf_get_days_emp'), 'IsDeterministic');

DBA/Developer should not remove the reference to the GETDATE() built-in function in the function's definition. Although this would make the function deterministic, DBA/Developer would not be able to calculate the number of days the employee has been employed. DBA/Developer should not create a view that includes only the function's result and create an index on the view because DBA/Developer cannot create a view that references a nondeterministic function. In this scenario, DBA/Developer would have to create a view based on each value returned from the function. Unlike a UDF or stored procedure, DBA/Developer cannot pass a parameter to a view. DBA/Developer should not create a computed column in the Employee table that uses the function in its expression definition and create an index on the computed column. Computed columns are virtual columns that are not physically stored in the table by default. Each computed column uses the AS keyword followed by an expression that evaluates to a value. The expression can contain constants, functions, operators, and references to other columns within the table. The value of the computed column is calculated each time a query that references it executes. DBA/Developer can also include the optional PERSISTED keyword when creating a computed column. When a persisted computed column is created, it is physically stored in the table and is recalculated each time a column value referenced in the calculation expression is changed. To be able to create an index on a computed column, the column must be deterministic and precise. A computed column is considered deterministic if it produces the same value each time it is passed the same values. A computed column is considered precise if it does not perform floating-point calculations using either a float or real data type. In this scenario, the function is non-deterministic, so using it in the computed column's expression would prevent DBA/Developer from creating an index on the computed column. DBA/Developer can query the IsDeterministic and IsPrecise properties using the COLUMNPROPERTY function to determine if a computed column is deterministic and precise, respectively.