User-Defined Functions

T-SQL has three flavours of user-defined function: scalar UDFs, inline table-valued functions (iTVFs), and multi-statement table-valued functions (mstvFs). They differ dramatically in performance — pick carefully.

Scalar UDFs

Returns a single value.

CREATE OR ALTER FUNCTION hr.fn_full_name (@id INT)
RETURNS NVARCHAR(101)
AS
BEGIN
    DECLARE @name NVARCHAR(101);

    SELECT @name = first_name + N' ' + last_name
    FROM   hr.employees
    WHERE  employee_id = @id;

    RETURN @name;
END;
GO

SELECT employee_id, hr.fn_full_name(employee_id) AS name
FROM   hr.employees;

The Scalar UDF Performance Trap

Pre-2019, scalar UDFs were a notorious performance killer:

• Forced row-by-row execution — scanned even one millions-row table sequentially.
• Disabled parallelism for the whole query.
• Hidden in execution plans (no per-row cost shown).

A simple SELECT fn_full_name(employee_id) FROM employees could be 100× slower than the equivalent inline expression.

Scalar UDF Inlining (SQL Server 2019+)

SQL Server 2019 introduced scalar UDF inlining — the optimiser tries to rewrite the UDF body as inline expressions, restoring set-based execution and parallelism.

-- Check if your UDF is inlineable
SELECT  name, is_inlineable
FROM    sys.sql_modules m
JOIN    sys.objects o ON o.object_id = m.object_id
WHERE   o.type = 'FN';

-- Force inlining off (for testing)
ALTER FUNCTION hr.fn_full_name (@id INT)
RETURNS NVARCHAR(101) WITH INLINE = OFF
AS
BEGIN
    ...
END;

Inlining requirements: deterministic, no SELECT INTO, no table variables, no time-dependent built-ins (per the long checklist in MS Docs). Many real-world scalar UDFs do not qualify and remain slow.

Inline Table-Valued Functions (iTVFs)

The recommended T-SQL function form. Body is a single SELECT — the optimiser inlines it like a parameterised view.

CREATE OR ALTER FUNCTION hr.fn_employees_by_dept (@dept_id INT)
RETURNS TABLE
AS RETURN (
    SELECT  employee_id, first_name, last_name, salary
    FROM    hr.employees
    WHERE   department_id = @dept_id
);
GO

-- Behaves like a view that takes a parameter
SELECT * FROM hr.fn_employees_by_dept(50);

-- Compose with CROSS APPLY
SELECT  d.department_name, e.*
FROM    hr.departments d
CROSS APPLY hr.fn_employees_by_dept(d.department_id) e;

Because iTVFs are inlined, the optimiser sees right through them, picks indexes from the underlying tables, and can parallelise. Performance is essentially identical to writing the SELECT directly.

Multi-Statement TVFs (mstvFs)

Looks similar but uses a BEGIN ... END body with intermediate logic:

CREATE OR ALTER FUNCTION hr.fn_pay_history (@id INT)
RETURNS @result TABLE (
    effective_date DATE,
    salary         DECIMAL(10,2),
    delta_pct      DECIMAL(6,2)
)
AS
BEGIN
    INSERT INTO @result (effective_date, salary, delta_pct)
    SELECT  effective_date, salary,
            (salary - LAG(salary) OVER (ORDER BY effective_date))
            / NULLIF(LAG(salary) OVER (ORDER BY effective_date), 0) * 100
    FROM    hr.salary_history
    WHERE   employee_id = @id;

    RETURN;
END;
GO

SELECT * FROM hr.fn_pay_history(100);

The mstvF Cardinality Estimate Caveat

Pre-2017, the optimiser always estimated 1 row from a multi-statement TVF — leading to terrible join plans for larger result sets. SQL Server 2017+ adds interleaved execution for mstvFs (compatibility level 140+), which executes the function once during compilation and uses the actual cardinality. Better, but still not as good as iTVFs.

Default to inline TVFs for any function returning a rowset.

Determinism and SCHEMABINDING

A function is deterministic if the same inputs always produce the same outputs (no GETDATE(), no NEWID(), no reading session state).

WITH SCHEMABINDING locks the function to its referenced objects — they cannot be dropped or altered while the function depends on them. Schemabound deterministic functions can be used in indexed computed columns and indexed views.

CREATE OR ALTER FUNCTION hr.fn_initcap (@s NVARCHAR(100))
RETURNS NVARCHAR(100)
WITH SCHEMABINDING
AS
BEGIN
    -- Trivial title-case: capital first letter, lower the rest
    RETURN UPPER(LEFT(@s, 1)) + LOWER(SUBSTRING(@s, 2, 99));
END;
GO

-- Now safe to use in a persisted computed column
ALTER TABLE hr.employees
    ADD display_name AS hr.fn_initcap(first_name + N' ' + last_name) PERSISTED;

Check determinism:

SELECT  name,
        OBJECTPROPERTYEX(object_id, 'IsDeterministic')   AS is_deterministic,
        OBJECTPROPERTYEX(object_id, 'IsSchemaBound')     AS is_schema_bound
FROM    sys.objects
WHERE   type IN ('FN','TF','IF');

Inline TVF for "Reusable Filters"

Inline TVFs replace the temptation to use scalar UDFs in WHERE clauses:

-- Reusable, performant
CREATE OR ALTER FUNCTION hr.fn_active_emps()
RETURNS TABLE
AS RETURN (
    SELECT *
    FROM   hr.employees
    WHERE  termination_date IS NULL
);
GO

SELECT  d.department_name, COUNT(*) AS active_staff
FROM    hr.departments d
CROSS APPLY hr.fn_active_emps() e
WHERE   e.department_id = d.department_id
GROUP BY d.department_name;

Best Practices

• Default to inline TVFs for any function returning a rowset.
• Avoid scalar UDFs unless inlining qualifies AND you have measured the impact.
• Inline expressions in SELECT / WHERE rather than wrapping them in scalar UDFs.
• Multi-statement TVFs only when the logic genuinely cannot be a single SELECT.
• Add WITH SCHEMABINDING for deterministic helpers used in indexed views or computed columns.

Summary

• Three function flavours: scalar UDF (slow), inline TVF (fast — preferred), multi-statement TVF (mediocre).
• Scalar UDFs got better with 2019 inlining but still risky — check is_inlineable.
• Inline TVFs are inlined like views — full optimiser visibility, parallelism, indexable columns.
• mstvFs estimated 1 row pre-2017; better with interleaved execution but still inferior to iTVFs.
• WITH SCHEMABINDING enables use in indexed computed columns and indexed views.