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

Question

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 1

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.

Answer 2

the BOL actually states:

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.

and then below it states what conditions must be met to make them deterministic.

E.g.

CAST - Deterministic unless used with datetime, smalldatetime, or sql_variant

In other words you need to meet those condition to use them in deterministic manner

For example when you create a table

CREATE TABLE [dbo].[deterministicTest](
    [intDate] [int] NULL,
    [dateDateTime] [datetime] NULL,
    [castIntToDateTime]  AS (CONVERT([datetime],[intDate],0)),
    [castDateTimeToInt]  AS (CONVERT([int],[dateDateTime],0)),
    [castIntToVarchar]  AS (CONVERT([varchar],[intDate],0))
) ON [PRIMARY]

you can apply index on castIntToVarchar but if you try to add index to castDateTimeToInt or castIntToDateTime you will get the following error:

Column 'castDateTimeToInt'(castIntToDateTime) in table 'dbo.deterministicTest' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

So the dateTime cannot be used neither as a source nor the target format of the CONVERT function if you want to stay deterministic

Answer 3

An example:

RAND(1)  // deterministic, always returns the same number

versus:

RAND()   // non-deterministic, returns new random number on each call

Note this uses the MSDN article's definition of the word "deterministic"