Deterministic and non-deterministic functions in SQL Server

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 different result sets for same input set of values in a given database state are known as non-deterministic functions.

SELECT GETDATE()

Any time you will run the above code, you will get a different output. This is an example of non-deterministic function. Below is the list of some other non-deterministic functions.

GETDATE, NEWID, ROW_NUMBER, RANK, RAND, @@CONNECTIONS, @@MAX_CONNECTIONS, @@TOTAL_READ, @@TOTAL_WRITE etc.

Do share and rate this article if you like it and don’t forget to put your comments below.