Previous Section Table of Contents Next Section

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 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-as 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.

    Previous Section Table of Contents Next Section