An expert's design errors

"Every query is correct. It is the assembly that fails: fifteen errors of global vision, and the method that avoids each one."

≈ 15 min readReading

Fifteen errors

A · The loop boundary (E1–E3)
B · The model is not ready (E4–E7)
C · Time and consistency (E8–E10)
D · The assembly neutralizes the engine (E11–E15)

Measurements of several of them: disaster cases.

On this page

Knowing the engine perfectly is no protection. The errors that follow do not come from ignorance of T-SQL: every query is correct. They come from a lack of global vision, that is, from a process designed query by query. For each one: the symptom, why an expert makes it, the effect on the whole, and the method that avoids it.

	Error	Family	Method in one line
E1	The invisible outer loop	Loop boundary	The parameter becomes a column; a “set” version of each procedure
E2	The scalar link	Loop boundary	Pure inline block, or function materialized as a table
E3	The trigger that iterates	Loop boundary	One statement, `OUTPUT` to trace
E4	The forgotten access path	Model not ready	Design for the global process, not for the loop
E5	The recomputed derivation	Model not ready	Materialize during preparation
E6	The rule in the code	Model not ready	Rules as data, joined
E7	Types that do not line up	Model not ready	Type contract for the model
E8	The shifting context	Time and consistency	Frozen inputs
E9	Implicit order and uniqueness	Time and consistency	Unique tiebreaker, single source
E10	The stale preparation	Time and consistency	Explicit invalidation and regeneration
E11	Neutralized parallelism	Assembly	Audit the plan; real work tables
E12	Skewed estimates	Assembly	Compile after loading
E13	The wrongly sized transaction	Assembly	Batches of sets by key ranges
E14	The repeated “just in case”	Assembly	Uniqueness by construction; keys first
E15	The missing check	Assembly	Check plan written beforehand

A. The loop boundary#

E1 · The invisible outer loop#

Symptom: A perfectly set-based procedure, called once per customer, per day or per entity, by a cursor, a job, an ETL loop or an ORM (“N+1”).
Why an expert makes it: In review, the procedure is beyond reproach; you do not see the loop, which is in the caller. The test is done on a single customer: a few milliseconds.
Global effect: The process is in fact row-by-row at the upper level: N calls, N plans, N commits, N trigger firings.
Method: Promote the parameter to a column: the entity identifier becomes a join key, a GROUP BY or PARTITION BY key. Provide, for every procedure, a “set” version that takes a set (table parameter or scope table). Forbid a procedure that is called in a loop from being the only version available.

-- Before: a procedure processes ONE customer; an orchestrator calls it N times
EXEC dbo.p_ValoriserClient @ClientId = 42;

-- After: the “set” version takes the whole scope, the parameter becomes a column
CREATE TYPE dbo.t_Ids AS TABLE (Id int NOT NULL PRIMARY KEY);
GO
CREATE PROCEDURE dbo.p_Valoriser @Clients dbo.t_Ids READONLY AS
    INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
    SELECT c.ClientId, c.Jour, c.Quantite * tj.Taux
    FROM   dbo.Consommations AS c
    JOIN   @Clients          AS p  ON p.Id = c.ClientId
    JOIN   dbo.Clients       AS cl ON cl.ClientId = c.ClientId
    JOIN   dbo.TauxParJour   AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

E2 · The scalar link#

Symptom: A non-inlinable scalar function, a multi-statement table-valued function, a procedure called per row, in the middle of a set-based process.
Why an expert makes it: The function is readable, reusable, indexed; you think of it as “set-based” because it contains a SELECT. It is inlined on SQL Server 2019 under conditions, and a single detail (two RETURN statements, a CTE, a time-dependent function) is enough to make it non-inlinable.
Global effect: Iterative execution, with no parallelism, with poorly estimated costs (Microsoft documentation): the entire process inherits the constraint of the weakest link.
Method: A pure block (inline table-valued function or expression) or, better, materialize the function: compute its result over the useful domain during preparation, then join. Check is_inlineable instead of assuming it, and the plan instead of trusting the documentation.

-- Function called per row
SELECT c.ClientId, c.Jour, c.Quantite * dbo.f_TauxApplicable(cl.SegmentId, c.Jour)
FROM   dbo.Consommations AS c JOIN dbo.Clients AS cl ON cl.ClientId = c.ClientId;

-- Truth table of the function, over its useful domain (segments x days)
SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
FROM   dbo.Consommations AS c
JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

-- The engine tells you whether a scalar function is inlinable; do not assume it
SELECT OBJECT_NAME(object_id) AS fonction, is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'dbo.f_TauxApplicable');

E3 · The trigger that iterates#

Symptom: A correct trigger (written to process all of inserted) that fires on every single-row call, or that re-reads other tables row by row.
Why an expert makes it: The trigger is set-based; what is not is the way it is invoked.
Global effect: N firings, N audit writes, N commits; the audit can weigh as much as the process itself.
Method: A single statement that writes and traces (OUTPUT … INTO). When the trigger is kept, invoke it once, with a write of the whole set.

-- Audit without a per-call trigger: one statement writes and traces at the same time
INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
OUTPUT inserted.ClientId, inserted.Jour, inserted.Montant INTO dbo.Audit_Valo (ClientId, Jour, Montant)
SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
FROM   dbo.Consommations AS c
JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

B. The model is not ready#

E4 · The forgotten access path#

Symptom: A table organized for data entry (by date, by arrival order) or for single-row access; the loop queries it per entity and reads the whole table on every turn, or requires an index “added for the loop”.
Why an expert makes it: You optimize what you see: the loop's query. The missing index is added, and the problem seems solved.
Global effect: The cost grows like number of entities × table size: quadratic. Even with the index, the loop reads N times, where the global process reads once.
Method: Design the keys and indexes for the global process (ordered scan, hash or merge joins), and check that each table is read once (SET STATISTICS IO: “Scan count”). Do not add an index to save a loop; remove the loop.

-- The global process reads each table ONCE: check the plan (one scan per table)
SET STATISTICS IO ON;
-- ... run the process, then read “Scan count” and “logical reads” for each table.
-- A “Scan count” proportional to the number of entities processed gives away a loop (or an index compensating for it).

-- Index designed for the process: the clustering order follows the process's read order
CREATE TABLE dbo.Consommations
(
    ClientId int NOT NULL, Jour date NOT NULL, Quantite decimal(18,3) NOT NULL,
    CONSTRAINT PK_Conso PRIMARY KEY CLUSTERED (Jour, ClientId)     -- time series: append + scan by period
);

E5 · The recomputed derivation#

Symptom: A hierarchy traversed by a recursive CTE for each node, permissions recomputed at each evaluation, a calendar rebuilt on every call, a reference join replayed in six queries.
Why an expert makes it: Each calculation is correct and fast on its own; nobody measures how many times it is redone.
Global effect: The same work is repeated N times; the total far exceeds what a single calculation, queried by join, would cost.
Method: Materialize during preparation the derivations used more than once: closure table, access table, calendar, persisted columns, indexed views. Regenerate it when its source changes (see E10).

-- Hierarchy closure: computed ONCE, queried by join
CREATE TABLE dbo.Fermeture (Ancetre int NOT NULL, Descendant int NOT NULL, Profondeur tinyint NOT NULL,
                            CONSTRAINT PK_Fermeture PRIMARY KEY CLUSTERED (Ancetre, Descendant));
WITH F AS
(
    SELECT NoeudId AS Ancetre, NoeudId AS Descendant, CAST(0 AS tinyint) AS Profondeur FROM dbo.Noeuds
    UNION ALL
    SELECT f.Ancetre, n.NoeudId, CAST(f.Profondeur + 1 AS tinyint) FROM F AS f JOIN dbo.Noeuds AS n ON n.ParentId = f.Descendant
)
INSERT INTO dbo.Fermeture (Ancetre, Descendant, Profondeur) SELECT Ancetre, Descendant, Profondeur FROM F OPTION (MAXRECURSION 200);

E6 · The rule in the code#

Symptom: CASE expressions, IF statements or functions that encode rate schedules, thresholds, validity periods.
Why an expert makes it: It is quick to write, and the code is “the source of truth” in its author's mind.
Global effect: A rule cannot be joined, compared or checked; every change is a deployment, and the rule is recomputed row by row.
Method: Rules as data: rate-schedule tables, with consistency constraints; expansion during preparation (intervals → one row per day); join.

-- Interval-based rate schedule, stored as data
CREATE TABLE dbo.Taux (SegmentId tinyint NOT NULL, DateDebut date NOT NULL, Taux decimal(9,4) NOT NULL,
                       CONSTRAINT PK_Taux PRIMARY KEY (SegmentId, DateDebut));
-- Expansion: one row per segment and per day, computed during preparation
WITH T AS (SELECT SegmentId, DateDebut, Taux, LEAD(DateDebut) OVER (PARTITION BY SegmentId ORDER BY DateDebut) AS DateFin FROM dbo.Taux)
INSERT INTO dbo.TauxParJour (SegmentId, Jour, Taux)
SELECT t.SegmentId, d.Jour, t.Taux
FROM   T AS t JOIN dbo.JoursPerimetre AS d ON d.Jour >= t.DateDebut AND (t.DateFin IS NULL OR d.Jour < t.DateFin);

E7 · Types that do not line up#

Symptom: int keys on one side, bigint or text on the other; different collations; date versus datetime; parameters declared with a different type from the column.
Why an expert makes it: Each query is checked in isolation; the type contract between steps belongs to nobody.
Global effect: Implicit conversions at the boundaries: an index is no longer used as expected, a seek becomes a scan, estimates degrade.
Method: A type contract for the model: a key has the same type everywhere, including in parameters, work tables and table types. Check the conversion warnings in plans.

-- Type contract: the key has the same type everywhere; so do the parameters
CREATE TABLE dbo.Clients   (ClientId int NOT NULL PRIMARY KEY, Nom nvarchar(100) NULL);
CREATE TABLE dbo.Commandes (CommandeId int NOT NULL PRIMARY KEY, ClientId int NOT NULL);   -- not bigint, not varchar
-- A comparison between different types forces the engine to convert: the lower-precedence column
-- may be converted row by row, and the index is no longer used as expected.
-- The execution plan shows it: warning “Type conversion in expression (CONVERT_IMPLICIT) ...”

C. Time and consistency#

E8 · The shifting context#

Symptom: GETDATE() called in several steps, reading the base table at each step, NEWID() in a sort key.
Why an expert makes it: Each step is deterministic at the moment it runs; it is the sequence that is not.
Global effect: A process that crosses midnight, or that reads while others write, gives a result that depends on the moment: impossible to replay, compare or test.
Method: Freeze the inputs: a single business date passed as a parameter, an input set as a table parameter, reads under consistent isolation when several reads must see the same state.

-- A single business date, supplied by the caller, used everywhere
CREATE PROCEDURE dbo.p_Traiter @DateExploitation date AS
BEGIN
    INSERT INTO dbo.Etape1 (ClientId, Jour, Quantite) SELECT ClientId, Jour, Quantite FROM dbo.Source WHERE Jour <= @DateExploitation;
    INSERT INTO dbo.Etape2 (ClientId, Jour, Quantite) SELECT ClientId, Jour, Quantite FROM dbo.Etape1 WHERE Jour <= @DateExploitation;
END;
-- Avoid: CAST(GETDATE() AS date) in each step (a process that crosses midnight changes its result)
-- To freeze the READS too: read under consistent isolation (SNAPSHOT) or start from a table parameter

E9 · Implicit order and uniqueness#

Symptom: A non-unique ORDER BY, a TOP without ordering, an update whose source has several matches, a ROW_NUMBER with ties.
Why an expert makes it: The result seems stable in testing: the plan has stayed the same. Nothing enforces it.
Global effect: The result changes with the plan, the volume or the parallelism: an equivalence test passes and then fails though nothing was changed.
Method: A unique tiebreaker in every ordering, a single source guaranteed by a constraint, a reproducibility test (two executions, EXCEPT).

-- Unique ordering: a tie must never decide the result
SELECT ContactId, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY DateCreation DESC, ContactId DESC) AS Rang FROM dbo.Contacts;

-- Single source: an update's join must return only one row per target
ALTER TABLE dbo.Categories ADD CONSTRAINT UQ_Categories_Code UNIQUE (Code);

-- Reproducibility test: two executions, same result
-- (copy the first result into a table, replay, then EXCEPT in both directions)

E10 · The stale preparation#

Symptom: A materialized table (access, closure, rates per day) that no longer reflects its source; or, conversely, materializing everything with no strategy.
Why an expert makes it: In the enthusiasm of preparation, you materialize; the question “who regenerates, and when?” comes afterward.
Global effect: Wrong but fast results: the worst case, because they are not visible.
Method: Explicit invalidation and regeneration: each derivation has a source, a regeneration trigger (the load that modifies the source), a freshness check. Materialize only what is reused and not very volatile.

-- Versioned preparation: the derivation is explicitly regenerated when its source changes
CREATE PROCEDURE dbo.p_Preparer_Acces AS
BEGIN
    TRUNCATE TABLE dbo.Acces;
    INSERT INTO dbo.Acces (UtilisateurId, PerimetreId)
    SELECT DISTINCT a.UtilisateurId, d.PerimetreId
    FROM   dbo.Appartenances AS a
    JOIN   dbo.GroupeFermeture AS gf ON gf.Groupe = a.GroupeId
    JOIN   dbo.DroitsGroupes   AS d  ON d.GroupeId = gf.Ancetre;
    UPDATE STATISTICS dbo.Acces WITH FULLSCAN;
END;
-- Who calls it? The load that modifies Groupes, Appartenances or DroitsGroupes — never the process itself.
-- Freshness check: compare the expected row count with that of the prepared table.

D. The assembly neutralizes the engine#

E11 · Neutralized parallelism#

Symptom: A process that uses only one core; an unexplained “serial” plan.
Why an expert makes it: The faulty detail is far from the query: a scalar function, a modified table variable, a MAXDOP 1 hint copied from an example.
Global effect: The process loses its only scalability lever without anyone noticing.
Method: Audit the plan: reason for non-parallelism, scalar functions, modified table variables (the documentation states that queries that modify a table variable are not parallelized). Replace with real, indexed work tables created during preparation.

-- Modified table variable: no parallel plan (documentation); real, indexed work table, created during preparation
CREATE TABLE dbo.Travail_Etape1 (ClientId int NOT NULL, Jour date NOT NULL, Montant decimal(18,4) NOT NULL,
                                 CONSTRAINT PK_Travail_Etape1 PRIMARY KEY (ClientId, Jour));
-- Look for the reason for a non-parallel plan: the NonParallelPlanReason attribute of the XML plan,
-- or a non-inlined scalar function in the query.

E12 · Estimates skewed by the sequencing#

Symptom: A plan that is excellent “in test” and bad in production: an estimate of one row where there are a million.
Why an expert makes it: The plan is built before the work table is loaded, or the work table is a table variable: without statistics, the optimizer assumes it is empty (Microsoft documentation). Each query is correct; it is the moment of compilation that is not.
Global effect: Unsuitable join algorithms, underestimated memory, spills: the engine has optimized “well” for data that does not exist.
Method: Compile after loading: separate preparation and execution (two routines), update statistics after loading, targeted OPTION (RECOMPILE), “hard-coded” values (see code generation).

-- Separate preparation and execution: execution is compiled AFTER loading
EXEC dbo.p_Preparer;        -- creates, loads, indexes the work tables
UPDATE STATISTICS dbo.TauxParJour WITH FULLSCAN;
EXEC dbo.p_Executer;        -- its plans see the real volumes
-- If both were in the same procedure, queries on objects still empty at compile time
-- would be estimated on empty tables (or recompiled along the way).

E13 · The wrongly sized transaction#

Symptom: A single statement of several hundred million rows that saturates the log and blocks everything; or, at the other extreme, one commit per row.
Why an expert makes it: Both extremes come from the same oversight: the log and the locks are a resource of the process, to be sized.
Global effect: A log that explodes, long locks, or thousands of commits.
Method: Batches of sets: key ranges aligned with the clustering index, one transaction per batch, restart possible (checkpoint recorded). Very few queries does not mean “no batches”; it means “never a one-row batch”.

-- Batches of SETS: key ranges aligned with the clustering index, one transaction per batch, restart possible
DECLARE @min int = (SELECT MIN(ClientId) FROM dbo.Clients),
        @max int = (SELECT MAX(ClientId) FROM dbo.Clients),
        @pas int = 100000;
WHILE @min <= @max
BEGIN
    INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
    SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
    FROM   dbo.Consommations AS c
    JOIN   dbo.Clients AS cl ON cl.ClientId = c.ClientId
    LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour
    WHERE  c.ClientId >= @min AND c.ClientId < @min + @pas;
    SET @min += @pas;      -- the batch is a set of 100,000 customers, not one customer
END;

E14 · The repeated “just in case”#

Symptom: DISTINCT, ORDER BY, GROUP BY at every step; SELECT * propagated; the same subquery repeated in several CTEs.
Why an expert makes it: Each precaution is local and harmless; they add up.
Global effect: Needless sorts and groupings at every step, materialization of wide columns, repeated calculations (a CTE is not a cache).
Method: Uniqueness by construction (keys, constraints) rather than a precautionary DISTINCT; sort only at output; propagate the keys and join the attributes at the end of the chain; materialize what is used several times.

-- Propagate the keys, join the attributes at the end of the chain
SELECT s.ClientId, s.Jour, s.Montant, cl.Nom, cl.Region        -- the wide columns come last
FROM  (SELECT ClientId, Jour, SUM(Montant) AS Montant FROM dbo.Valorisations GROUP BY ClientId, Jour) AS s
JOIN   dbo.Clients AS cl ON cl.ClientId = s.ClientId;
-- A derivation used more than once is materialized, not repeated in several CTEs (a CTE is not a cache)

E15 · The missing check#

Symptom: A loop replaced by a query, validated “because it works on the test set”.
Why an expert makes it: The satisfaction of having removed the loop makes you forget to prove the equivalence.
Global effect: A silent difference (NULLs, duplicates, ties) discovered months later.
Method: Check plan written beforehand: counts, invariants, equivalence in both directions against the old version, replayability.

-- Check plan written BEFORE the process
-- 1) invariants: counts and sums
SELECT (SELECT COUNT(*) FROM dbo.Consommations) AS entrees, (SELECT COUNT(*) FROM dbo.Valorisations) AS sorties;
-- 2) equivalence with the old version, in both directions (no rows expected)
SELECT * FROM dbo.Valorisations_ref EXCEPT SELECT * FROM dbo.Valorisations;
SELECT * FROM dbo.Valorisations EXCEPT SELECT * FROM dbo.Valorisations_ref;
-- 3) replayability: second execution, same result

Sources and verification#

Microsoft Learn — Scalar UDF Inlining — iterative execution, lack of parallelism, inlining conditions
Microsoft Learn — table (Transact-SQL) — table variables: statistics, parallelism
Microsoft Learn — Query processing architecture guide — estimates, parameters, cached plans
Microsoft Learn — Use table-valued parameters — table parameters