Deterministic and Nondeterministic Functions
Functions can be either deterministic or nondeterministic. A deterministic function always returns the same results if given the same input values. A nondeterministic function may return different results every time it is called, even when the same input values are provided.
Why is it important that a given input always returns the same output? It is important because of how functions may be used within views, in user-defined functions, and in stored procedures. Restrictions vary across implementations, but these objects sometimes allow only deterministic functions within their defining code. For example, SQL Server allows the creation of an index on a column expression, but only if the expression does not contain any nondeterministic functions. Rules and restrictions vary between the platforms, so check your platform’s documentation when using functions.
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
