"The approach: prepare, then strike once"

Using the engine is not enough: you must organize the process so that the engine has everything it needs at the moment you ask it the question.

≈ 9 min readReading

In brief

Before: scope, grain, keys, indexes, pure and materialized functions, rules as data, derivations, frozen inputs, checks.
During: very few, very large queries, no iteration over rows.
Deterministic throughout: one sequential or non-deterministic link breaks the chain; an excellent block inside a cursor is no longer worth anything.
Deliberate: decisions are made before writing.

On this page

1. Two mindsets
2. Two phases: prepare, then strike once
3. What is prepared beforehand
4. Executing: very few, very large queries
5. The whole process must be deterministic
1. 5.1 A chain is only as strong as its weakest link
2. 5.2 A deterministic block inside a cursor, and all the gains are lost
6. A deliberate approach: the questions before the first query
7. Work tables, table variables and in-memory tables
8. Cost and limits of the approach
Sources and verification

The engine does only half the work. The first half of set-based programming is using its tools well (joins, window functions, APPLY…): that is the subject of the previous pages. The second is an approach: organizing the process so that the engine has, at the moment you finally ask it the question, everything it needs. You prepare the model beforehand; you then issue very few, very large queries.

Measurements cited in this section

The measurements in this section (“Disaster cases” pages) were taken on SQL Server 2019 (LocalDB). The number of statements executed, the number of function calls and the logical reads are reproducible exactly. Durations and CPU time, however, depend on the machine and vary from one run to another (up to a factor of 2 on my machine): read them as orders of magnitude.

1. Two mindsets#

Question	“Query” mindset	“Process” mindset
Where does the cost lie?	At each execution, spread across many statements	Paid during preparation, once; execution is mundane
What do you optimize?	Each query, in isolation	The entire process, from input to result
How many statements?	Dozens, thousands, sometimes one per entity	A few, very large, with no iteration
Where does the logic live?	In the code: `CASE`, functions, loops	In the model: rule tables, keys, indexes, deterministic objects
What do you test?	One case, one customer, one date	The whole set, and equivalence with the previous version
When do you decide?	While writing	Before writing: grain, scope, derivations, checks

A developer who knows the engine perfectly can remain in the first mindset: every query they write is excellent. Yet performance is decided in the assembly.

2. Two phases: prepare, then strike once#

The cost is paid in phase 1, once; phase 2 is deliberately mundane.

The preparation phase is long and deliberate: you spend time on it without looking for a result. The execution phase is short and mundane: if it is difficult, the preparation is incomplete.

3. What is prepared beforehand#

To prepare	Why	Method
The scope	A single definition of the “what”; no more repeated, diverging filters	Scope table (keys of the entities to process) or table parameter; joined everywhere
The grain and the keys	Almost all errors come from a poorly defined grain	Primary and foreign keys; uniqueness by construction rather than a precautionary `DISTINCT`
The global access path	The physical order must serve the entire process, not a single-row lookup	Clustering keys aligned with the processing order, covering indexes, statistics updated after loading
Pure functions	Deterministic, indexable, inlinable, replayable blocks	Inline table-valued functions, `SCHEMABINDING`, no non-deterministic calls (see Determinism)
Materialized functions	Replace a per-row call with a join	The function becomes a truth table over its useful domain (days × segments, codes × labels…)
Rules, as data	Modifiable without redeploying, joined like everything else	Rate schedules and rules in tables, validated by constraints, possibly expanded (intervals → one row per day)
Structural derivations	Hierarchies, permissions, calendars would otherwise be recomputed everywhere	Closure table, access table, calendar, persisted columns, indexed views
Work tables	Typed, indexed, right-sized intermediate spaces	Work tables created and indexed during preparation; non-durable memory-optimized tables for staging; see §7
Frozen inputs	A reproducible process has inputs that do not move while it runs	Business date as a parameter, input set as a table parameter, reads under consistent isolation
The check plan	Know what a correct result looks like before producing it	Counts, sums, invariants, `EXCEPT` against the previous version

4. Executing: very few, very large queries#

No iteration over data rows. A loop is justified only over objects (see the limits) or batches of sets.
Each query processes the whole scope, not one element at a time: yesterday's parameter is today's column.
One write per target table and per step, with OUTPUT to trace what changes, rather than a trigger that scans again.
An explicit order between steps (a dependency graph, not a historical pile-up); each step declares what it reads and what it writes.
Batches only because of a physical constraint (log, locks, duration), bounded by key ranges aligned with the index, never “one row at a time”.
Replayable: rerunning the process on the same inputs gives exactly the same result.

5. The whole process must be deterministic#

“Deterministic” takes its broadest meaning here: same inputs, same outputs, at any time, with no hidden dependency (time, randomness, implicit order, state of other transactions, execution plan). This is what makes a process preparable (what you materialize remains valid), replayable, testable and comparable to its previous version.

5.1 A chain is only as strong as its weakest link#

Just one sequential or non-deterministic link is enough to hurt the whole. Amdahl's law (1967) gives the order of magnitude: if a fraction s of the work remains sequential, the speedup is bounded by 1/s, whatever the number of cores. With p = 1 − s the parallelizable fraction and n cores: S = 1 / ((1 − p) + p/n).

Sequential fraction of the process	Maximum speedup (unlimited cores)	with 8 cores
50%	× 2	× 1.8
10%	× 10	× 4.7
5%	× 20	× 5.9
1%	× 100	× 7.5

And the loop is worse than the law: the “sequential” fraction is not constant, it grows with the number of iterations, because each turn pays a fixed overhead.

5.2 A deterministic block inside a cursor, and all the gains are lost#

A perfectly written block (an inline table-valued function, an indexed query, a pure function) remains excellent on its own. Invoked N times by a cursor, it suffers the cost of iteration:

What a block costs in a loop	Why this cancels the block's optimization
The call itself, on every turn	Plan lookup, opening the execution context, passing parameters: a fixed overhead, multiplied by N
One commit per turn (autocommit)	Each statement commits separately and waits for the log write
Tiny sets	No more hash joins or single scans: N scattered lookups, or N scans if the access path was not designed for this
No more parallelism, no more batch mode	Each execution is too small, and some constructs (non-inlined scalar functions) prevent parallelism
Statistics and estimates “one at a time”	Each plan is good for one entity; nobody optimizes the sum

The block's optimization is local; the loop's cost is global. The measurements below compare, for three cases, the “best-optimized” loop (indexes added, clean queries) with the set-based approach:

Case (page)	“Best-optimized” loop	Set-based version	Structural gap
Valuation (2,000 customers)	188,002 statements · 180,000 function calls · 3.6M logical reads (index added for the loop)	4 statements · 0 calls · 0.76M logical reads	× 47,000 statements · no calls at all
Hierarchy (20,000 nodes)	40,002 statements · 1.8M logical reads (index present)	2 statements · 1.7M reads — then 1 statement for each recalculation (about 13 times fewer reads)	× 20,000 statements
Permissions (40 users)	200,082 statements · 200,000 calls · 59M logical reads	3 statements · 0 calls · 14,000 reads	× 4,000 in logical reads

Reading the figures

Statements, function calls and logical reads are structural measurements: they do not depend on the machine. The time gap, on the other hand, varies with volume, environment and concurrency; in the cases measured, it ranges from a few units to several hundred depending on size, because the loop spends in proportion to the number of iterations, and sometimes to its square (see the case pages).

6. A deliberate approach: the questions before the first query#

What is the grain of the result? What is a row?
What is the scope? Can it be materialized as a table of keys?
Which inputs must be frozen? Which business date, which snapshot?
What is stable, what changes often? What is stable gets materialized; what changes at every execution gets joined.
Which derivations will be used more than once? Hierarchies, permissions, calendars, mappings.
Where is the hidden loop? A caller, an orchestrator, a trigger, a scalar function?
How will we know the result is correct? The check plan is written before the process.
What does the final query look like? If you do not know how to write it, the preparation is not finished.

7. Work tables, table variables and in-memory tables#

Object	What the documentation says	Use in the approach
Table variable	No distribution statistics; the optimizer often assumes no rows; queries that modify it are not parallelized; no recompilation is triggered; deferred compilation with compatibility level 150; it is not an “in-memory only” structure (it lives in `tempdb`)	Small sets only (a few dozen rows); not for large working volumes
Temporary table	Full indexes and statistics; recompilations possible	Work table of significant volume, indexed before use
Non-durable memory-optimized table (`DURABILITY = SCHEMA_ONLY`)	Transient data with no disk I/O or logging; suited to ETL staging and to replacing `tempdb` structures; SQL Server 2014 and later; not a “magic button” (of little use for aggregates over large ranges, where columnstore is preferred)	Fast staging between steps, with no log cost
Memory-optimized table type	Table parameters and table variables that inherit the benefits of non-durable tables	Pass a set to a routine with no I/O
Natively compiled module	Procedures, triggers and scalar functions can be compiled to native code	Deterministic block with a very low unit cost, when sequential logic is unavoidable

8. Cost and limits of the approach#

Cost of the approach	Remedy
Preparation time (design, loading, indexing)	It is the accepted cost; it is amortized over each execution and each new use of the same preparation
Storage of materialized derivations	Materialize only what is reused; narrow types; compression
Freshness: the preparation can become stale	Explicitly invalidate and regenerate when the source changes (see E10)
Increased deployment complexity	Preparation = versioned, replayable, tested scripts; deterministic generators (code generation)
Cases where it does not apply: low-latency single-row access, tiny volumes, external effects, reference data that changes at every execution	Keep it simple: set-based is a tool, not a dogma (see the limits)

The concrete errors this approach helps avoid are catalogued in An expert's design errors, and measured in Disaster cases.

Sources and verification#

Amdahl, G. M. (1967), “Validity of the single processor approach to achieving large scale computing capabilities”, AFIPS Conference Proceedings, vol. 30, pp. 483–485 — Amdahl's law
Microsoft Learn — table (Transact-SQL): table variables — statistics, parallelism, deferred compilation, tempdb
Microsoft Learn — In-Memory OLTP: overview and usage scenarios — non-durable tables, memory-optimized table types, native modules
Microsoft Learn — Scalar UDF Inlining — iterative execution and lack of parallelism of scalar functions