Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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. – Aaron Bertrand Mar 21 '12 at 14:54
Where could I document myself about this, on the web? – Mihai Mar 21 '12 at 14:57
up vote 5 down vote accepted

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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