User Defined Functions (UDF)

UDFs allow you to create reusable logic inside SQL Server. However, there is a massive performance difference between Scalar Functions (returns one value) and Table-Valued Functions (returns a result set). Choosing the wrong one can kill your server.

1. The Scalar Function Trap

Scalar UDFs are historically slow in SQL Server. If you use a Scalar UDF in a SELECT on 1 million rows, SQL Server must call that function 1 million times, row-by-row. This prevents **Parallelism** and turns your query into a slow serial process.

2. Inline Table-Valued Functions (iTVF)

An **Inline** TVF is essentially a "Parameterized View". The SQL Optimizer can "Expand" the function and merge it into the main query, keeping it extremely fast and allowing for full index usage.

CREATE FUNCTION dbo.GetActiveUsers(@MinSpend DECIMAL)
RETURNS TABLE AS RETURN (
    SELECT * FROM Users WHERE LifetimeSpend > @MinSpend
)

4. Interview Mastery

Q: "What is 'UDF Inlining' in SQL Server 2019+?"

Architect Answer: "In SQL Server 2019, Microsoft introduced a feature that automatically tries to 'Inline' simple Scalar UDFs, converting them into subqueries under the hood. This can result in 10x-100x performance gains for legacy code. However, it only works for simple functions. For complex logic, you should still manually convert Scalar functions to Inline Table-Valued functions for maximum reliability."