Deterministic Scalar Functions

Question

I want to index a computed column in my database table which uses a number of user defined functions. However I just learned that my column is non-deterministic.

Without indexing its going to be way to slow to run queries that I need.

What's the best way of tracing through user defined functions to determine whether they are deterministic?

Is there any kind of tool in SQL Server Management Studio that will tell me whether a user defined function is deterministic or do I just need to trace through all the system-defined functions I'm using to find out which are non-deterministic and find other ways to write my code without them?

Answer 1

Try:

SELECT  *
FROM    INFORMATION_SCHEMA.ROUTINES
WHERE   IS_DETERMINISTIC = 'NO'
        AND ROUTINE_TYPE = 'FUNCTION'

or

SELECT OBJECTPROPERTY(OBJECT_ID('schemaname.functionname'), 'IsDeterministic')

Sorry, IIRC, SQL Server 2000 did not allow non-deterministic UDFs.

Indexing a column that is non-deterministic is pretty of silly - after all if its value is not strictly dependent on parameters, it's not going to be very useful if it changes on you all willy-nilly, especially if it's used in an index to find things!

Answer 2

Since you say you're using a number of user-defined functions, have you tried creating a function-based index on each one separately? That should at least narrow it down.

Answer 3

I figured out why my function is non-deterministic. It relies on Convert statements with style codes 1, 3, and then others above 100.

According to msdn Convert is:

Deterministic unless one of these conditions exists:

Source type is sql_variant.

Target type is sql_variant and its source type is nondeterministic.

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.