What does it mean by "Non-deterministic User-Defined functions can be used in a deterministic manner"?

sql sql-server user-defined-functions deterministic non-deterministic
dance2die picture dance2die · Apr 2, 2009 · Viewed 14.9k times · Source

According to MSDN SQL BOL (Books Online) page on Deterministic and Nondeterministic Functions, non-deterministic functions can be used "in a deterministic manner"

The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

What does it mean by non-deterministic functions can be used in a deterministic manner?
Can someone illustrate how that can be done? and where you would do so?

Answer

Tor Haugen picture Tor Haugen · Apr 2, 2009

That a function is deterministic means that it is guaranteed always to return the same output value for the same input arguments.

Using a non-deterministic function in a deterministic manner I assume means that you ensure that the range of arguments you will pass to the function is such that the return value will be deterministic, ie. dependent only opon those arguments.

What this implies in practice depends on what the function does and in what way it is non-deterministic.

Related questions

SQL User Defined Function Within Select

I have a user defined function in SQL called getBuisnessDays it takes @startdate and @enddate and returns the number of business days between the two dates. How can I call that function within my select? Here's what I'd like to …

Read More
How to determine the number of days in a month in SQL Server?

I need to determine the number of days in a month for a given date in SQL Server. Is there a built-in function? If not, what should I use as the user-defined function?

Read More
Multi-statement Table Valued Function vs Inline Table Valued Function

A few examples to show, just incase: Inline Table Valued CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER …

Read More