Answer
Scalar functions often look clean and reusable but can be hidden performance killers in production workloads.
Reasons they degrade performance:
- They are executed once per row in a result set, turning set-based operations into row-by-row computation.
- The optimizer cannot see inside the function body, treating it as a black box. This prevents many optimizations and accurate cardinality estimates.
- In many SQL Server versions, the presence of scalar functions in the SELECT or WHERE list can disable parallelism, forcing single-threaded plans for otherwise parallelizable queries.
- They frequently increase CPU usage and query duration dramatically on large datasets.
Mitigations include:
- Refactoring to inline TVFs or pure set-based SQL.
- Replacing scalar functions with computed columns (possibly indexed) when appropriate.
- Inlining logic into the main query where feasible.