I'd like to factor out some of the functionality in our SPs into separate functions/stored procedures, but I'm worried about the potential performance penalty. In particular, how efficient will be a join with a table-valued function, as opposed to writing out the query from that function in full?

  • 1
    Depends on how the function is being used. In the select clause, where clause, cross apply, assigning variables, ...? In some cases it will be optimized away, in others it won't. Mar 21 '12 at 14:54
  • Where could I document myself about this, on the web?
    – Mihai
    Mar 21 '12 at 14:57

An in-line table valued function (TVF) is simply a macro that expands into the containing query: no more, no less. Just like a standard view.

A multi-statement TVF on the other hand has to run to completion and won't be optimised for subsequent operations on the results

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.