Answer
SQL Server supports several function types, each with distinct performance and usage characteristics.
Scalar functions:
- Return a single scalar value (e.g.,
INT,VARCHAR). - Evaluated per row when used in queries, often resulting in RBAR (Row-By-Agonizing-Row) execution.
- Act as black boxes to the optimizer, frequently preventing parallelism and leading to severe performance issues.
Inline table-valued functions (iTVFs):
- Return a table defined by a single
RETURN (SELECT ...)statement. - Logically similar to parameterized views.
- Fully inlined into the calling query, enabling the optimizer to generate efficient set-based plans with good cardinality estimates.
- Usually the best-performing function type for set-based logic.
Multi-statement table-valued functions (mTVFs):
- Declare an internal table variable and populate it across multiple statements.
- By default, SQL Server assumes a fixed row estimate (historically 1 row), often leading to poor plans.
- Limited statistics and cardinality information, frequently causing spills and suboptimal joins.
For performance-sensitive code, prefer inline TVFs over scalar or multi-statement TVFs whenever possible.