Skip to content
Related Articles

Related Articles

Deterministic and Nondeterministic Functions in SQL Server
  • Last Updated : 22 Sep, 2020

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 :



ABSDATEDIFF
POWERACOS
DAYRADIANS
ASINDEGREES
ROUNDATAN
EXPSIGN
ATN2FLOOR
FLOORSIN
CEILINGISNULL
SQUARECOALESCE
ISNUMERICSQRT
DATEADDNULLIF
COSLOG10
LOGYEAR
TANDATALENGTH
COTMONTH

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 :

@@CONNECTIONSLAG
@@DBTSLAST_VALUE
@@IDLELEAD
@@CPU_BUSYMIN_ACTIVE_ROWVERSION
@@IO_BUSYNEWID
@@PACK_RECEIVEDNEWSEQUENTIALID
@@MAX_CONNECTIONSNEXT VALUE FOR
@@PACK_SENTNTILE
@@PACKET_ERRORSPARSENAME
@@TIMETICKSPERCENTILE_CONT
@@TOTAL_ERRORSPERCENTILE_DISC
@@TOTAL_READCUME_DIST
@@TOTAL_WRITEPERCENT_RANK
GETUTCDATEFORMAT
GETDATERAND
GET_TRANSMISSION_STATUSDENSE_RANK
CURRENT_TIMESTAMPFIRST_VALUE
RANKROW_NUMBER

My Personal Notes arrow_drop_up
Recommended Articles
Page :