SQL Server – Are UDFs Optimized/Merged in Stored Procedures?

optimizationquerysql server

The issue has sent me crazy for a few days, I may have found my answer, but I need a confirmation and to check for an eventual easy solution.

When a stored procedure uses some user defined functions, are the UDF plans merged in the main plan and everything get optimised or are they called directly every time?

And suppose it doesn't happen, is there a way to force SQL Server to do it for a specific SP?

Best Answer

If I understand the question correctly, it should depend on the type of function. Inline Table-Valued Functions have their definitions substituted into referencing queries, just like what happens with views. This is what is meant by "inline", and why they are not true modules that can be signed, among other differences with the other function types.

Multi-statement Table-Valued Functions and Scalar User-Defined Functions are completely independent modules that are executed separately and have their own plans.

This behavior should have nothing to do with them being referenced in a stored procedure or in an ad hoc query.

Now, whether or not UDFs are called every time, that depends on the type of UDF:

  • T-SQL UDFs are executed per row and prevent parallel plans
  • SQLCLR UDFs, if they are marked as IsDeterministic=true; (is false by default), and are not doing any data access, do not prevent parallel plans and should be able to have their return values cached (per query execution, I assume).

All of this is why, whenever possible, it is best to convert T-SQL UDFs into T-SQL Inline TVFs.

Related Question