Common Table Expressions

Status: Draft (deep-dive v1)
Stack note: Warehouse usp_* boards, pagination helpers, and hierarchy walks (org trees, Agent job steps, recursive path expansion) read cleaner with WITH CTEs — still set-based, still Blazor-callable procs.
Pairs with: Temp vs table vars, APPLY & set-based, Pagination.
Goal: Use non-recursive and recursive CTEs well; know when #temp / @table still win.

Quick chooser

Need Prefer Why
Name a subquery once, reference 2+ times Non-recursive CTE Readable; one definition
Page a board (keyset / numbered) CTE + OFFSET/FETCH or keyset (see SQL-25) Clear stages: filter → number → slice
Walk a hierarchy / graph (parent→child) Recursive CTE Built-in; no cursor
Large intermediate you index / reuse many times #temp (SQL-13) Stats + indexes; CTE is single-statement scope
Tiny in-memory map inside a proc @table (SQL-13) Light; CTE still fine if one statement
Per-outer-row table logic APPLY + iTVF (SQL-17) Not a CTE job

Non-recursive CTE (WITH)

WITH Filtered AS (
  SELECT a.AlertId, a.SqlInstanceId, a.SeverityCode, a.FirstSeenAt
  FROM dbo.Alert a
  WHERE a.Status IN (N'Open', N'Acked')
),
Ranked AS (
  SELECT f.*,
         ROW_NUMBER() OVER (
           PARTITION BY f.SqlInstanceId
           ORDER BY f.SeverityCode DESC, f.FirstSeenAt DESC
         ) AS rn
  FROM Filtered f
)
SELECT SqlInstanceId, AlertId, SeverityCode, FirstSeenAt
FROM Ranked
WHERE rn <= 5;

Rules of thumb - A CTE lives for one statement (the SELECT/INSERT/UPDATE/DELETE that follows the WITH list). - You can chain multiple CTEs separated by commas — later CTEs can read earlier ones. - Referencing the same CTE twice is OK; the optimizer may spool or inline (don’t assume “runs once”).

Pagination sketch (ties to SQL-25)

WITH Page AS (
  SELECT i.SqlInstanceId, i.HostName, i.EnvironmentCode,
         ROW_NUMBER() OVER (ORDER BY i.HostName, i.SqlInstanceId) AS rn
  FROM dbo.SqlInstance i
  WHERE i.IsActive = 1
)
SELECT SqlInstanceId, HostName, EnvironmentCode
FROM Page
WHERE rn BETWEEN @StartRn AND @EndRn
ORDER BY rn;

Prefer keyset pagination for hot Blazor boards (SQL-25); use numbered CTEs for admin/report pages where simplicity beats perfect seek plans.

Recursive CTE

WITH Walk AS (
  -- anchor
  SELECT j.JobId, j.ParentJobId, j.JobName, 0 AS Lvl,
         CAST(j.JobName AS nvarchar(4000)) AS Path
  FROM dbo.AgentJob j
  WHERE j.ParentJobId IS NULL

  UNION ALL

  -- recursive member
  SELECT c.JobId, c.ParentJobId, c.JobName, w.Lvl + 1,
         CAST(w.Path + N' > ' + c.JobName AS nvarchar(4000))
  FROM dbo.AgentJob c
  INNER JOIN Walk w ON c.ParentJobId = w.JobId
  WHERE w.Lvl < 20   -- guardrail
)
SELECT JobId, Lvl, Path
FROM Walk
ORDER BY Path;

Must-haves - Anchor + UNION ALL + recursive member that references the CTE name. - Depth guard (Lvl < N) or a cycle-break column — runaway recursion is a real outage. - Default MAXRECURSION is 100; override carefully: OPTION (MAXRECURSION 0) only with a hard stop in the query.

Cycle-safe pattern (sketch)

Carry a delimited path of IDs and refuse to re-enter:

AND CHARINDEX(N',' + CAST(c.JobId AS nvarchar(20)) + N',', w.IdPath) = 0

CTE vs #temp vs @table

Concern CTE #temp @table
Scope One statement Proc/session Batch/proc
Stats / indexes Generally no (inline/spool) Yes Weak / limited
Multi-step: load → index → join → update Awkward Natural Awkward for large sets
Readability of staged logic Excellent Good OK
Hierarchy walk Recursive CTE Possible but heavier Rarely

Practical split for warehouse procs 1. Shape / filter / page in a CTE when it stays one statement. 2. Dump to #temp when you need indexes, multi-step mutation, or reuse across statements (SQL-13). 3. Keep APPLY for per-row TVF work (SQL-17) — don’t force that into recursion.

Ops checklist

See also