Deterministic and non-deterministic functions in SQL Server 7


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

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

Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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