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.

Very simple and definite explanation.

Valuable information…Thanks for sharing..

Thanks to all for your comments and suggestions. Please keep posting and encourage sharing of knowledge. Thanks!

Thanks for your sharing.

It’s so good.

Thanks for sharing

Very simple and definite explanation.

Thanks for your compliment Suresh Kumar. Keep posting your comments and feedback.