Deterministic/Nondeterministic Functions

  • Why string functions CHARINDEX and PATINDEX are nondeterministic?

  • This definition is taken from BOL

    quote:


    All functions are deterministic or nondeterministic:

    Deterministic functions always return the same result any time they are called with a specific set of input values.

    Nondeterministic functions may return different results each time they are called with a specific set of input values.


    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Because, unlike the other string functions, they can return different results dependent upon the database compatibility level.

    --Jonathan



    --Jonathan

  • Ya Frank, I get the definition from Books OnLine. But the actual reason is not given there.

    Thanks Jonathan for your answer. I will check those functions' return values by changing the database compatibility level.

  • Sorry, actually more information you'll find in BOL under PATINDEX()

    quote:


    ...

    Remarks

    PATINDEX is useful with text data types; it can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause).

    If either pattern or expression is NULL, PATINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.

    ...


    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply