Open In App

Deterministic and Nondeterministic Functions in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

1. Deterministic functions :
Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database. For example, AVG() function always results the same result given the qualifications stated above.

2. Nondeterministic functions :
Nondeterministic functions result in different output each time they are called with a fixed set of input values even if the database state that they access remains the same. For example, GETDATE() function, results the current date and time value, always a different value.

Built-in Function Determinism :
You cannot impact the determinism of any built-in function. The built-in function might be deterministic or nondeterministic based on the property of the function, implemented by SQL Server. For example, using an ORDER BY clause in any query do not change the determinism of the function that is used in the query.

Deterministic built-in functions are :

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

The below functions are not always deterministic but could be deterministic when they are specified in a deterministic manner:

  • CAST is deterministic until used with DateTime, smalldatetime, or sql_variant.
  • ISDATE is deterministic only in case used with CONVERT function.
  • CONVERT is deterministic until one of these conditions exists.
    1. Source type may be sql_variant.
    2. Target type may sql_variant & its source type is nondeterministic.

Nondeterministic built-in functions are :

@@CONNECTIONS LAG
@@DBTS LAST_VALUE
@@IDLE LEAD
@@CPU_BUSY MIN_ACTIVE_ROWVERSION
@@IO_BUSY NEWID
@@PACK_RECEIVED NEWSEQUENTIALID
@@MAX_CONNECTIONS NEXT VALUE FOR
@@PACK_SENT NTILE
@@PACKET_ERRORS PARSENAME
@@TIMETICKS PERCENTILE_CONT
@@TOTAL_ERRORS PERCENTILE_DISC
@@TOTAL_READ CUME_DIST
@@TOTAL_WRITE PERCENT_RANK
GETUTCDATE FORMAT
GETDATE RAND
GET_TRANSMISSION_STATUS DENSE_RANK
CURRENT_TIMESTAMP FIRST_VALUE
RANK ROW_NUMBER


Last Updated : 22 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads