Matt Miller (1/23/2008)
The other place not yet mentioned where this gets even more important to consider is when you use a table-valued function within a CROSS APPLY clause. It's in those cases where it sets up something like a "correlated sub-function".Again - very powerful potential, but will tend to quickly turn into something not at all efficient on the execution plan side.
GilaMonster (1/23/2008)
One of the things to watch for with table-valued functions (especially the multi-statement table valued functions) is that the resultant table, just like a table variable) has no column statistics and no indexes. The optimiser doesn't know how many rows are in the function's result table. That's not an issue for small numbers of rows, but it becomes a very big issue for larger numbers of rows.If the optimiser misjudges the number of rows, it can result in very poor query plans.
Note, this is a maybe, possibly, could be, not definitely and always. Test carefully.
I'm assuming that those and the other statements talk about table functions in general, and I believe they are true for non-Inline table functions.
But I'm very interessed to hear what you guys think about Inline Table Functions.
if we use inline table functions, would we have performance decrease?
As far as I understand, when an SQL statement references an Inline Table Function,the parser and query optimizer analyze the source of both the SQL statement and the Inline Table Function and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the Inline Table Function (please correct me if I'm wrong). If this is true, would we still have any reasons to believe that an Inline Table Function may hurt the performance?
Thanks,
Luiz.