domingo, julho 31, 2011

Deterministic and Nondeterministic Functions

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.

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

Nenhum comentário: