Deterministic and Nondeterministic Functions
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.