Limit Use of Multi-Statement TVFs and Table Variables

Limit use of multi-statement table valued functions (TVFs) and table variables in situations where getting a high-performance plan is requied. Both multi-statement TVFs and table variables have no statistics. The optimizer must guess the size of their results. Similarly, result column do not have statistics, and the optimizer must resort to guesses for predicates involving these columns. If a bad plan results because of these guesses, consider using an in-lined TVF, or standard table or temporary table as a temporary holding place for the results of the TVF, or a replacement for the table variable. This will allow the optimizer to use better cardinality estimates. Note that this technique may also lead to an increased number of statement recompilations.