Functions are specified with parentheses even when there is no parameter. Exceptions to this are the niladic functions that are used with the DEFAULT keyword. Niladic functions do not take parameters. For more information about the DEFAULT keyword, see ALTER TABLE (Transact-SQL) and CREATE TABLE (Transact-SQL).

The parameters to specify a database, computer, login, or database user are sometimes optional. If they are not specified, the default is the current database, host computer, login, or database user.

In SQL Server 2005, functions are classified as strictly deterministic, deterministic, or nondeterministic.

A function is strictly deterministic if, for a specific set of input values, the function always returns the same results.

For user-defined functions, a less rigid notion of determinism is applied. A user-defined function is deterministic if, for a specific set of input values and database state, the function always returns the same results. If the function is not strictly deterministic, it can be deterministic in this sense if it is data-accessing.

A nondeterministic function may return different results when it is called repeatedly with the same set of input values. For example, the function GETDATE() is nondeterministic. SQL Server puts restrictions on various classes of nondeterminism. Therefore, nondeterministic functions should be used carefully.

For built-in functions, determinism and strict determinism are the same. For Transact-SQL user-defined functions, the system verifies the definition and prevents the definition of nondeterministic functions. However, a data-accessing or nonschema-bound function is considered not strictly deterministic. For common language runtime (CLR) functions, function definitions specify the deterministic, data access, and system data access properties of the function, but because these properties are not system-verified, the functions are always considered not strictly deterministic.

The lack of determinism of a function can limit where it can be used. Only deterministic functions can be invoked in indexed views, indexed computed columns, persisted computed columns, or definitions of Transact-SQL user-defined functions.

The lack of strict determinism of a function can block valuable performance optimizations. Certain plan reordering steps are skipped to conservatively preserve correctness. Additionally, the number, order, and timing of calls to user-defined functions is implementation-dependent. Do not rely on these invocation semantics. Aside from runtime constant nondeterministic built-ins RAND, and the GETDATE family, the number, order, and timing of calls will depend on the plan chosen.

Best Practice Recommendations

We recommend that you following the following guidelines whenever possible:

1. Write strictly deterministic functions when you have a choice. In particular, make your Transact-SQL functions schema-bound.