Daily Archives: Nov 18, 2014


Deterministic and non-deterministic functions in SQL Server 6

In this topic “Deterministic and non-deterministic functions”, we are going to discuss the deterministic and non-deterministic functions in sql server. Lets start with “what is a deterministic function in sql server?”. It’s a common interview question also. I will elaborate the definition of deterministic as well as non-deterministic functions and also provide some good examples. So after reading this article you will be able to distinguish deterministic and non-deterministic functions.

Deterministic Functions

The functions which always return same output when they called with a particular set of input values in the same state of the database are known as deterministic functions.

SELECT ISNULL('ABCDEF', '1')

Above t-sql code will always return the output as ‘ABCDEF’ and thus ISNULL is a deterministic function. Below is the list of some other deterministic functions.

ISNULL, ISNUMERIC, DATEDIFF, POWER, CEILING, FLOOR, DATEADD, DAY, MONTH, YEAR, SQUARE, SQRT etc.

Non-deterministic Functions

The functions which may return … More