Encourage use of UDFs instead of Procedures when possible?

Over the past few months one of the issues that repeats in this forum has to do with the use of stored procedure output as input to other procedures. One method of dealing with this is to convert the stored procedure to a function if possible and use the function for the process.

I was wandering if it would be a good idea to change my database standards to reflect a preference to use functions over stored procedures -- especially in a read-only context. I realize that in some cases this might cause performance problems, but I sill want to recruit opinions on this issue.

Anybody?

Dont do it....

Functions will use row by row operations (Like a cursor)

sprocs will perform set-based operations.

we are dealing with that at my current position. Removing UDFs in favor of subqueries or sproc calls.

|||Thank you.|||

UDFs come in 3 flavors - scalar UDFs, inline table-valued functions and non-inline table-valued functions. The performance of the TVFs goes in the following order:

#1. Inline TVFs - Since the TVF definition is parsed into the query, compiled and optimized. This is similar to how views are resolved in a query

#2. Non-inline TVFs - Cost and Row estimates will be inaccurate due to use of table variable. So plan choices are limited and performance will suffer in most cases

Next, the scalar UDFs are a different beast altogether. In general, it should be avoided in queries (WHERE clause, SELECT list etc) since it introduces performance problems and bad plans. Often inlining the expression in the scalar UDFs in queries will give orders of magnitude performance benefits and better plans. In SQL Server 2005, you can use WITH SCHEMABINDING on scalar UDFs so that the query optimizer knows whether a UDF is performing data access or not. This can result in slightly better performance. The main thing to note about UDFs is that you are doing a row-by-row call in queries and there is lot of overhead to that. For CPU and calculation intensive scalar UDFs, you should consider migrating to CLR UDFs since those are compiled and provide better performance for the complex ones.

So the choice of converting the SP logic to UDFs should consider above points. If you want to reuse results then consider moving the queries to views or inline TVFs. If you want to reuse the logic and calculations then try the CLR UDFs for complex calculations or just inline the calculations in queries for best performance.

|||

Thank you

Umachandar:

You stated, "... The main thing to note about UDFs is that you are doing a row-by-row call in queries and there is lot of overhead to that. ..." I want to make sure I clearly understand this. You mean this for all function types or is this quote only in the context of scalar UDFs?

|||It is true for scalar UDFs. But depending on the query plan, you will encounted similar situation for multi-statement TVFs. For example, this will happen if you have a nested loop join. In this case, you will end up querying the TVF for each row in the input and this will be bad because there is no statistics on the table variable used in the multi-statement TVF.|||

Perfect. Thank you both for your responses.

:-)