Geek Answers Handbook

Deterministic scalar functions

I want to index a computed column in my database table that uses a number of custom functions. However, I just found out that my column is non-deterministic.

Without indexing, this will be a way to slow down the queries I need.

What is the best way to track through user-defined functions to determine if they are deterministic?

Is there any tool in SQL Server Management Studio that will tell me if a user-defined function is deterministic or do I just need to trace all the system functions that I use to find out deterministic and find other ways to write my code without them?

0
sql-server ssms
source share
3 answers

Since you say that you use several custom functions, have you tried creating a functional index for each separately? That should at least cut it.

+1
source share

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 deterministic UDFs.

Indexing a column that is not deterministic is rather stupid - because if its value does not depend strictly on the parameters, it will not be very useful if it changes for you all willingly, especially if it is used in the index to find things!

+2
source share

I understood why my function is not deterministic. He relies on Convert expressions with style codes 1, 3, and then others in excess of 100.

According to msdn Convert:

Deterministic if one of these conditions does not exist:

Source type is sql_variant.

The target type is sql_variant, and its source type is non-deterministic.

The source or destination type is datetime or smalldatetime, the other source or destination type is a character string, and a non-deterministic style is specified. To be deterministic, the style parameter must be constant. In addition, styles less than or equal to 100 are non-deterministic, with the exception of styles 20 and 21. Styles over 100 are deterministic, with the exception of styles 106, 107, 109 and 113.

0
source share

More articles:


All Articles