You are here
Home > Sql server > Sql server 2016 > Deterministic & Non-deterministic Functions

Deterministic & Non-deterministic Functions

Deterministic & Non-deterministic Functions

In this post we will discuss about Function determinism. Function can be deterministic or non-deterministic.

  • Function determinism is a characteristic that indicates whether the function is guaranteed to return the same result given the same set of input values in different invocations.
  • If the function provides such a guarantee, then this type of function is called deterministic functions. Otherwise, it is called non-deterministic function.
  • There are three categories of Function determinism:
  1. There are some functions that are always deterministic.
  2. There are some functions that are deterministic when invoked in a certain way.
  3. There are some functions that are always non-deterministic.

Functions that are always deterministic:

  • Example of functions that are always deterministic: all string functions, COALESCE(), ISNULL(), ABS, SQRT() and many other.

Ex: ABS(-1889) always returns 1889

  • SELECT ISNULL (‘Riya’, ‘1’) always returns the same value, so it is deterministic.

Functions that are deterministic depending on how they used:

  • Some functions are deterministic depending on how they used. For example CAST(), CONVERT() functions are not deterministic because when converting from character string to date and time it depends on the login language.


select CAST(’03/10/17′ As DATE)) returns March 10,2017 under English, October 3,2017 under British and October 17, 2003 under Swedish and Japanese.

  • Other example is RAND() function it returns float value in the range 0 to 1. When it is used with seed it is deterministic otherwise non-deterministic.
  • When we use without seed value SQL server compute a new seed value based on the previous value.

Functions that are always Non-deterministic:

  • Some functions are always nondeterministic like SYSDATETIME and NEWID.
  • The first function returns the current date and time in datetime2 datatype and second returns the globally unique identifier as UNIQUEIDENTIFIER type.
  • GETDATE(), ROW_NUMBER(), RANK() functions are also non-deterministic.

Keep Visiting for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.
You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply