T-SQL User-Defined Functions

Status: Reviewed (deep-dive v1)
Stack note: UDFs live in SQL; PowerShell/Blazor should call stored procs for app paths — keep UDFs for set-friendly reuse inside T-SQL, not as the Blazor API surface.
Goal: Know scalar vs multi-statement TVF vs inline TVF, when each is safe, and the performance traps.

Quick chooser

Need	Prefer	Avoid
Reuse a expression in queries (can be inlined)	Inline TVF (`RETURNS TABLE AS RETURN SELECT …`)	Scalar UDF in `SELECT`/`WHERE` over big sets
Parameterized view-like shape	Inline TVF	Multi-statement TVF if a single SELECT works
Complex procedural logic with temp tables / many statements	Stored procedure (or carefully a multi-statement TVF)	Scalar UDF called per row
Compute once per row in app/API	Do it in proc or app layer	Scalar UDF from Blazor via ad-hoc SQL

The three kinds

1. Scalar UDF (`RETURNS datatype`)

CREATE FUNCTION dbo.udf_AddTax (@Amount money)
RETURNS money
AS BEGIN
  RETURN @Amount * 1.0825;
END;

Pros: Simple, familiar.
Cons (classic): Historically executed like a black box per row; blocks parallelism; hides cost in plans. Newer SQL Server versions can inline some scalar UDFs (Froid) — don’t rely on that for estate-wide habit.

Rule of thumb: OK for rare/admin scripts. Bad as SELECT dbo.udf_X(col) FROM bigtable. Prefer inline expression, computed column, or inline TVF joined/applied.

2. Inline TVF (iTVF)

CREATE FUNCTION dbo.itvf_OrdersForCustomer (@CustomerId int)
RETURNS TABLE
AS RETURN
(
  SELECT OrderId, OrderDate, Total
  FROM Sales.Orders
  WHERE CustomerId = @CustomerId
);

Pros: Optimizer can expand like a view; set-based; usually best UDF style for querying.
Cons: Single RETURN SELECT only — no procedural body.

Use: Parameterized reusable queries; CROSS APPLY dbo.itvf_…(t.Id).

3. Multi-statement TVF (mTVF)

CREATE FUNCTION dbo.tvf_ReportRows (@AsOf date)
RETURNS @t TABLE (Id int, Label nvarchar(100))
AS BEGIN
  INSERT @t SELECT …;
  -- more logic
  RETURN;
END;

Pros: Multi-step logic in one function.
Cons: Often poor cardinality estimates (table variable); can be much slower than inline TVF or a proc. Treat as last resort vs proc + temp table.

Performance checklist

[ ] No scalar UDF in hot SELECT/JOIN/WHERE on large sets
[ ] Prefer inline TVF over mTVF when possible
[ ] If mTVF: check actual rows vs estimates; consider rewriting as proc + #temp
[ ] Schema-bind views/iTVFs when you need deterministic indexed views (separate topic)
[ ] WITH SCHEMABINDING on UDFs when you want to lock dependencies / allow some optimizations
[ ] Deterministic vs non-deterministic: GETDATE() inside UDF limits indexed view / persisted computed use
[ ] Measure: SET STATISTICS IO/TIME; actual plan; Query Store regressions after deploy

When to use what (ops + app stack)

Scenario	Choice
Blazor needs data	Stored proc (`usp_…`) — matches SPIKE pattern
Same filter reused in many reports/procs	Inline TVF or shared view
One-off formatting / mask for DBA script	Scalar UDF acceptable
ETL row-by-row helper	Fix set-based design; don’t “UDF your way out”
Maintenance warehouse transforms	Inline TVF or proc steps in Agent job

Creation / change hygiene

Name: udf_ / itvf_ / tvf_ prefix (or team standard) + schema dbo or domain schema
Deploy via source control; no silent ALTER in prod
Grant EXECUTE on scalar; SELECT on TVFs to roles — least privilege
Document: parameters, null behavior, determinism
After change: smoke the callers (procs/jobs that reference it)

Common failure patterns

Symptom	Likely cause
Serial plan / high CPU on simple query	Scalar UDF per row
TVF returns slow with wrong memory grant	mTVF table-variable estimates
“Unexpected” GETDATE() values	Non-deterministic UDF in weird contexts
Can’t create indexed view	Non-schema-bound / non-deterministic function

Tiny lab (prove the point)

Big table + scalar UDF in SELECT → note duration/plan
Replace with inline expression or iTVF → compare
Keep the faster pattern as team default

Tie-ins

SQL-03 / SQL-07: Mystery CPU often traces to scalar UDFs in app SQL
SPIKE procs: Prefer procs for Blazor; UDFs optional inside proc SQL only when set-friendly

Done when

[ ] Team default documented: iTVF > mTVF > scalar for set queries
[ ] Hot-path audit: no scalar UDFs on top Query Store consumers
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-11 T-SQL User-Defined Functions v1