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 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.

Rate This
[Total: 0    Average: 0/5]


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.


Leave a Reply to Gopal Krishna Ranjan Cancel reply

Your email address will not be published. Required fields are marked *

6 thoughts on “Deterministic and non-deterministic functions in SQL Server