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?

  • Can you specify what kind of function? – Erik Darling Sep 13 '18 at 12:19

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.

  • Seems I asked the wrong question, but this is still a valuable piece of knowledge I needed to learn, so +1 and accepted answer. Thank you :-) – motoDrizzt Sep 14 '18 at 13:36
  • Thanks. But what was the actual thing you were wanting to know about? – Solomon Rutzky Sep 14 '18 at 13:43
  • I'll post the question in a moment. Seems I've got at hand a bad case of parameter sniffing without parameter sniffing. Or either I have to admit I have no idea how parameter sniffing happens. – motoDrizzt Sep 14 '18 at 13:45
  • 1
    if you are wanting to know if UDFs can have parameter sniffing issues, I believe the answer is implied in my answer here: Inline TVFs = no, Multi-statement TVFs and Scalar UDFs = yes. – Solomon Rutzky Sep 14 '18 at 13:48
  • It's more complicated than that. I have a stored procedure that run slow in a case and very fast in another, and after a few days of messing around I was (wrongly) convinced that it was because of the calls to functions. This DB has been designed to not being queried easily: so the SP calls a view which calls some functions which are calling some functions which are calling some functions, and all of them working on the same 4 tables. – motoDrizzt Sep 14 '18 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.