Tell me more ×
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. It's 100% free, no registration required.
up vote 2 down vote favorite

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
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. – 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

1 Answer

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

 
discard

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.