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 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a Reply to Sanchit Goel Cancel reply

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

*

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