Deterministic and Nondeterministic Functions in SQL Server

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

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.