Returning a Table Variable will make it a multi-statement table valued function and can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on - so it will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then therefore the plan generated could be a lot less than optimal.
Whereas, returning just a SELECT makes it an inline table valued function - think of it more as a view. In this case, the actual underlying tables get brought into the main query and a better execution plan can be generated based on proper statistics. You'll notice that in this case, the execution plan will NOT have a mention of the function at all as it's basically just merged the function into the main query.
There's a great reference on it on MSDN by CSS SQL Server Engineers including (quote):
But if you use multi-statement TVF,
it’s treated as just like another
table. Because there is no
statistics available, SQL Server has
to make some assumptions and in
general provide low estimate. If your
TVF returns only a few rows, it will
be fine. But if you intend to
populate the TVF with thousands of
rows and if this TVF is joined with
other tables, inefficient plan can
result from low cardinality estimate.
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
