Export (0) Print
Expand All

Deterministic and Nondeterministic Functions

SQL Server 2000

All functions are deterministic or nondeterministic:

  • Deterministic functions always return the same result any time they are called with a specific set of input values.

  • Nondeterministic functions may return different results each time they are called with a specific set of input values.

Whether a function is deterministic or nondeterministic is called the determinism of the function.

For example, the DATEADD built-in function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

Earlier versions of Microsoft® SQL Server™ have no functionality that is dependent on the determinism of functions. In Microsoft SQL Server 2000, nondeterministic functions cannot be specified in two types of Transact-SQL expressions:

  • An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions.

  • A clustered index cannot be created on a view if the view references any nondeterministic functions.

One of the properties SQL Server records for user-defined functions is whether the function is deterministic. A nondeterministic user-defined function cannot be invoked by either a view or computed column if you want to create an index on the view or computed column.

User-Defined Function Determinism

Whether a user-defined function is deterministic or nondeterministic depends on how the function is coded. User-defined functions are deterministic if:

  • The function is schema-bound.

  • All built-in or user-defined functions called by the user-defined function are deterministic.

  • The body of the function references no database objects outside the scope of the function. For example, a deterministic function cannot reference tables other than table variables that are local to the function.

  • The function does not call any extended stored procedures.

User-defined functions that do not meet these criteria are marked as nondeterministic. Built-in nondeterministic functions are not allowed in the body of user-defined functions.

Built-in Function Determinism

You cannot influence the determinism of any built-in function. Each built-in function is deterministic or nondeterministic based on how the function is implemented by Microsoft SQL Server.

All of the aggregate and string built-in functions are deterministic except the string functions CHARINDEX and PATINDEX. For a list of these functions, see Aggregate Functions and String Functions.

These built-in functions from categories of built-in functions other than aggregate and string functions are always deterministic:

ABS DATEDIFF PARSENAME
ACOS DAY POWER
ASIN DEGREES RADIANS
ATAN EXP ROUND
ATN2 FLOOR SIGN
CEILING ISNULL SIN
COALESCE ISNUMERIC SQUARE
COS LOG SQRT
COT LOG10 TAN
DATALENGTH MONTH YEAR
DATEADD NULLIF  

These functions are not always deterministic but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function Comments
CAST Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERT Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified.
CHECKSUM Deterministic, with the exception of CHECKSUM(*).
ISDATE Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
RAND RAND is deterministic only when a seed parameter is specified.

All of the configuration, cursor, meta data, security, and system statistical functions are nondeterministic. For a list of these functions, see Configuration Functions, Cursor Functions, Meta Data Functions, Security Functions, and System Statistical Functions.

These built-in functions from other categories are always nondeterministic:

@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME

Calling Extended Stored Procedures from Functions

Functions that call extended stored procedures are nondeterministic because the extended stored procedures can cause side effects on the database. Side effects are changes to a global state of the database, such as an update to a table, or to an external resource, such as a file or the network (for example, modifying a file or sending an e-mail message). You should not rely on returning a consistent result set when executing an extended stored procedure from a user-defined function. User-defined functions that create side effects on the database are not recommended.

When called from inside a function, the extended stored procedure cannot return result sets to the client. Any Open Data Services API that returns result sets to the client will have a return code of FAIL.

The extended stored procedure can connect back to SQL Server; however, the procedure cannot join the same transaction as the original function that invoked the extended stored procedure.

Similar to invocations from a batch or stored procedure, the extended stored procedure is executed in the context of the Microsoft Windows® security account under which SQL Server is running. The owner of the extended stored procedure should consider this when granting permissions to other users to execute the procedure.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft