Deterministic and Nondeterministic Functions

  • Article

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

There are several properties of user-defined functions that determine the ability of the SQL Server Database Engine to index the results of the function, either through indexes on computed columns that call the function, or through indexed views that reference the function. The determinism of a function is one such property. For example, a clustered index cannot be created on a view if the view references any nondeterministic functions. For more information about the properties of functions, including determinism, see User-defined Function Design Guidelines.

This topic identifies the determinism of built-in system functions and the affect on the deterministic property of user-defined functions when it contains a call to extended stored procedures.

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

All of the aggregate and string built-in functions are deterministic. For a list of these functions, see Aggregate Functions (Transact-SQL) and String Functions (Transact-SQL).

The following 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

 

The following 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 one of these conditions exists:

  • Source type is sql_variant.
  • Target type is sql_variant and its source type is nondeterministic.
  • Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

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 the configuration, cursor, metadata, security, and system statistical functions are nondeterministic. For a list of these functions, see Configuration Functions (Transact-SQL), Cursor Functions (Transact-SQL), Metadata Functions (Transact-SQL), Security Functions (Transact-SQL), and System Statistical Functions (Transact-SQL).

The following built-in functions from other categories are always nondeterministic.

@@CONNECTIONS

@@TOTAL_READ

@@CPU_BUSY

@@TOTAL_WRITE

@@DBTS

CURRENT_TIMESTAMP

@@IDLE

GETDATE

@@IO_BUSY

GETUTCDATE

@@MAX_CONNECTIONS

GET_TRANSMISSION_STATUS

@@PACK_RECEIVED

MIN_ACTIVE_ROWVERSION

@@PACK_SENT

NEWID

@@PACKET_ERRORS

NEWSEQUENTIALID

@@TIMETICKS

RAND

@@TOTAL_ERRORS

TEXTPTR

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.

See Also

Concepts

User-defined Function Design Guidelines
Table-valued User-defined Functions
Inline User-defined Functions
Rewriting Stored Procedures As Functions

Other Resources

Designing User-defined Functions

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History
Changed content:
  • Added information to the description of the CONVERT function that clarified that using CONVERT produces deterministic results unless the source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified.
  • Also clarified which styles are deterministic and nondeterministic.