APPLY & Set-Based Patterns

Status: Reviewed (deep-dive v1)
Stack note: Warehouse usp_* boards and merges should stay set-based — CROSS/OUTER APPLY with inline TVFs (SQL-11) replaces cursor / scalar-UDF-per-row habits. Blazor still calls procs (SQL-12); APPLY lives inside T-SQL.
Pairs with: SQL-11 (iTVF), SQL-13 (#temp), SQL-16 (indexes for joins/apply).
Goal: When to use APPLY, how it pairs with iTVFs, and how to kill row-by-row patterns.

Quick chooser

Need	Prefer	Avoid
Per-outer-row table-valued logic (top N, parameterized set)	`CROSS APPLY` + iTVF or subquery	Cursor / scalar UDF in SELECT
Same, but keep outers with no match	`OUTER APPLY`	Inner join that drops parents
Simple join on keys	Regular `JOIN`	APPLY for no reason
Reuse a parameterized SELECT	Inline TVF + APPLY	Multi-statement TVF / scalar
Many rows to transform	Set `INSERT…SELECT` / `#temp`	RBAR `WHILE`

CROSS vs OUTER APPLY

-- CROSS APPLY: like INNER — outer row must produce ≥1 apply row
SELECT i.SqlInstanceId, i.HostName, x.LastBackupAt, x.RagStatus
FROM dbo.SqlInstance i
CROSS APPLY dbo.itvf_BackupStatusForInstance(i.SqlInstanceId) x;

-- OUTER APPLY: like LEFT — keep instance even if TVF returns empty
SELECT i.SqlInstanceId, i.HostName, x.LastBackupAt, x.RagStatus
FROM dbo.SqlInstance i
OUTER APPLY dbo.itvf_BackupStatusForInstance(i.SqlInstanceId) x;

Mental model: APPLY invokes a table-valued expression once per outer row, correlated on outer columns. Optimizer can inline iTVFs like a parameterized view.

iTVF shape (best partner)

CREATE FUNCTION dbo.itvf_TopAlertsForInstance (@SqlInstanceId int, @Take int)
RETURNS TABLE
AS RETURN
(
  SELECT TOP (@Take) a.AlertId, a.Title, a.SeverityCode, a.FirstSeenAt
  FROM dbo.Alert a
  WHERE a.SqlInstanceId = @SqlInstanceId
    AND a.Status IN (N'Open', N'Acked')
  ORDER BY a.FirstSeenAt ASC
);

-- board: top 5 open alerts per instance
SELECT i.HostName, t.AlertId, t.Title, t.FirstSeenAt
FROM dbo.SqlInstance i
CROSS APPLY dbo.itvf_TopAlertsForInstance(i.SqlInstanceId, 5) t
WHERE i.IsActive = 1;

Why not scalar UDF: per-row black box (SQL-11). Why not mTVF: weak estimates — prefer iTVF or proc + #temp.

APPLY without a formal TVF

Inline derived table works too:

SELECT i.SqlInstanceId, b.LastBackupAt
FROM dbo.SqlInstance i
CROSS APPLY (
  SELECT TOP (1) s.LastBackupAt
  FROM dbo.SqlDatabaseBackupStatus s
  WHERE s.SqlInstanceId = i.SqlInstanceId
  ORDER BY s.LastBackupAt DESC
) b;

Prefer a named iTVF when the shape is reused across procs.

Set-based replacements (estate)

RBAR smell	Set-based move
Cursor over instances calling a proc	One proc: set join / APPLY / merge
Scalar UDF in SELECT list	Expression, computed col, or iTVF + APPLY
`WHILE` inserting one ack	Set `UPDATE` + set `INSERT` events (SQL-15 tran)
CSV split loop	TVP (SQL-13)
Per-row dynamic SQL	Parameterized set query (SQL-14)

Evaluate pattern (SPIKE-07): build #Findings in set inserts from inventory/backup/agent flags — then one merge to Alert. No cursor over instances unless unavoidable.

Performance habits

[ ] Prefer iTVF so APPLY can inline
[ ] Index for the APPLY’s WHERE/ORDER BY (SQL-16) — e.g. (SqlInstanceId, FirstSeenAt)
[ ] TOP + ORDER BY inside APPLY needs a supporting index or sorts blow up
[ ] Don’t nest APPLY five deep “because we can” — readability + plan cost
[ ] Compare APPLY vs window functions (ROW_NUMBER) for top-N-per-group — pick the clearer measured winner
[ ] Watch estimated vs actual on APPLY operator after data growth

Window alternative (top N per group)

;WITH ranked AS (
  SELECT a.*, ROW_NUMBER() OVER (
    PARTITION BY a.SqlInstanceId ORDER BY a.FirstSeenAt
  ) AS rn
  FROM dbo.Alert a
  WHERE a.Status IN (N'Open', N'Acked')
)
SELECT i.HostName, r.AlertId, r.Title
FROM dbo.SqlInstance i
JOIN ranked r ON r.SqlInstanceId = i.SqlInstanceId AND r.rn <= 5;

Use when the set is easier as one pass; use APPLY+iTVF when reuse/parameter packaging matters.

Common failure patterns

Symptom	Likely cause
Slow board with APPLY	mTVF / scalar / missing index on correlated cols
Missing parent rows	Used `CROSS` instead of `OUTER`
Same logic copy-pasted	Extract iTVF
Still “feels” row-by-row	Cursor or UDF hiding in view/proc

Tiny lab

Top-5 alerts per instance: cursor vs CROSS APPLY iTVF vs window — compare duration
Force bad plan with mTVF — rewrite to iTVF
Add supporting IX — re-check APPLY cost

Shortlist wrap (11→17)

#	Topic
11	UDFs
12	Stored procs
13	Temp vs table var
14	Dynamic SQL
15	Error handling
16	Indexing for procs
17	APPLY & set-based (this note)

Done when

[ ] Team default: no cursors/scalar UDFs on hot board paths without review
[ ] Reused per-row sets live as iTVF + APPLY (or window)
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-17 APPLY & Set-Based Patterns v1