December 30, 2003 at 6:43 am
Why string functions CHARINDEX and PATINDEX are nondeterministic?
December 30, 2003 at 6:46 am
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
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 30, 2003 at 7:34 am
Because, unlike the other string functions, they can return different results dependent upon the database compatibility level.
--Jonathan
--Jonathan
January 2, 2004 at 10:14 am
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.
January 2, 2004 at 12:44 pm
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
--
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