6

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?

dance2die
  • 35,807
  • 39
  • 131
  • 194

4 Answers4

9

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.

Tor Haugen
  • 19,509
  • 9
  • 45
  • 63
  • 1
    Dang! I gotta get a faster trigger finger! – Daniel Pratt Apr 02 '09 at 13:09
  • Actually BOL list all the functions that are not always deterministic, and specifies when they are and are not deterministic. Other functions are either always deterministic or always non-deterministic – kristof Apr 02 '09 at 13:15
9

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"

5

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

kristof
  • 52,923
  • 24
  • 87
  • 110
  • So that means, when I use CAST, which is non-determistic can be determistic, say when I do, say, "cast(field as int)"? – dance2die Apr 02 '09 at 13:12
  • @Sung Meister, CAST(field AS INT) is deterministic, the output is determined solely by the input. However, CAST(field AS DATETIME) is non-deterministic, the output depends not only on the input, but also potentially on externalities such as how the server is configured to handle two-digit years. – LukeH Apr 02 '09 at 13:22
  • @Luke also if the 'field' is of type dateTime it will be non-deterministic as well – kristof Apr 02 '09 at 13:40
  • @kristof, True. I meant to include that in my comment but ran out of characters! – LukeH Apr 02 '09 at 14:25
0

BOL definitions should read:

”Deterministic functions always return the same result on the same row any time they are called with a specific set of input values (row) and given the same state of the database.

In other words deterministic functions always return the same result on any particular fixed value from their domain (in this case domain is a row).

Nondeterministic functions may return different results each time they are called with a specific set of input values (row) even if the database state that they access remains the same.

In this case nondeterministic functions

a) Return different values every time they called.

b) Depend on the values outside of the row they applied on.

Examples of group a): NEWID(), GETDATE(), GETUTDATE(), RAND() with no seed specified.

Examples of group b): GET_TRANSMISSION_STATUS(), LAG(), RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(), SUM() when specified with the OVER and ORDER BY clauses. ”

Please note that some authors use different definition of deterministic functions which may lead to confusion.

Community
  • 1
  • 1
Pavel Nefyodov
  • 876
  • 2
  • 11
  • 29