4.1 Types of Functions
There are different ways to categorize functions into
groups. The following subsections describe distinctions that are
critical to understand how functions work.
4.1.1 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 userdefined 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
expressionas long as the expression does not contain
nondeterministic functions. Rules
and restrictions vary between the platforms, so check the specific
documentation when using functions.
4.1.2 Aggregate and Scalar Functions
Another way of categorizing functions is in terms of whether they
operate on values from just one row at a time, on values from a
collection, or on a set of rows. Aggregate
functions operate against a collection of values and
return a single summarizing value. Scalar
functions
return a
single value based on scalar input arguments. Some scalar functions,
such as CURRENT_TIME, do not require any
arguments.
4.1.3 Window Functions
Window functions
can be
thought of as being similar to aggregate functions in that they
operate over many rows at one time. The difference lies in how you
define those rows. Aggregate functions operate over the sets of rows
defined by a query's GROUP BY
clause. With window functions, you specify the set of rows for each
function call, and different invocations of a function within the
same query can execute over different sets of rows.
