SQL SERVER – Function Property – Deterministic or Non-Deterministic

I recently received question through email that how to determine if any user defined function is deterministic or non-deterministic?

First go through two articles I have written about deterministic and non-deterministic function.

SQL SERVER – Deterministic Functions and Nondeterministic Functions

SQL SERVER – 2005 – Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today

You can run following code to determine if function is deterministic or not.

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ufnGetAccountingStartDate'),
'IsDeterministic') IsFunctionDeterministic

Why It Matters if a Function Is Deterministic or Non-Deterministic

A deterministic function always returns the same result for the same input. DATEADD is one: add one day to the same date and you always get the same answer. GETDATE() is not, because it returns a different value each time you call it. Most of the time you do not need to care. It starts to matter when you want SQL Server to store or index the result.

Here is where it counts:

• A computed column can be persisted or indexed only if its expression is deterministic.
• An indexed view can only use deterministic functions.
• A user defined function used in either of those places must be deterministic too.

For your own T-SQL functions, there is one catch that surprises many people. SQL Server marks a user defined function as deterministic only if it is created WITH SCHEMABINDING. The same code without schema binding returns 0 from the check above, even if it only does simple math. So if the result is not what you expect, look at the CREATE FUNCTION statement first.

Two more small points. The check returns NULL when the name is wrong or the object is not a function, so check the spelling and the schema first. And if you only need the answer for a computed column, COLUMNPROPERTY with 'IsDeterministic' works on the column itself.

Built-in functions need the same care. Some of them depend on how you call them. CONVERT between strings and dates, for example, is deterministic only with certain style numbers, so check the documentation before you use it in a persisted computed column. Also remember that this property is about repeatable results, not about speed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.