Deterministic and nondeterministic functions: The basics
All functions, both built-in and UDF, are either deterministic or nondeterministic. A deterministic function always returns the same result. For example, when passed the same set of parameters, the DateAdd (built-in) function always returns the same date. The following statement
SELECT DateAdd("mm",1,OrderDate) FROM Orders
adds one month to the value held in the OrderDate field in the Northwind sample database, as shown in the figure below.

Result of a SELECT statement returns a predictable response

A deterministic function must meet all four of the following conditions:
  • ·        It can’t access any table other than those defined by the function.
  • ·        It can’t use built-in, nondeterministic functions.
  • ·        It can’t call nondeterministic UDFs.
It also can’t call extended stored procedures, which are external routines (DLLs) written in a programming language like C.