clement13.net
Original photo: Panneau « Speed limit 5 » sur le muret d'un parking en hauteur : une limite fixée, mais qui a sa raison d'être.
Description
Panneau « Speed limit 5 » sur le muret d'un parking en hauteur : une limite fixée, mais qui a sa raison d'être.
Source
MarkBuckawicki · original page
Licence
CC0 Public domain
Original photo, unretouched. The background is a version of it stylised by the site.

When procedural is legitimate

Judgment before dogma: where set-based stops, and what looks like it without being it.

≈ 3 min readReading
In brief
  • Legitimate: looping over objects, batches, external effects, irreducible sequential logic.
  • To avoid: scalar functions, multi-statement TVFs, correlated subqueries on large volumes, row-by-row triggers.
  • Test: "what does the loop iterate over?"
On this page
  1. When procedural is legitimate
  2. Fake set-based code
  3. Safeguards
  4. Sources and verification

Set-based is not a religion: it is a tool. This page marks its limits, to avoid the opposite mistake (forcing a set-based solution where it makes no sense) and to recognize fake set-based code.

When procedural is legitimate#

  • Looping over objects, not over data: rebuilding the indexes of each table, applying an operation to each database, walking through a list of parameters. Each iteration drives a set-based operation.
  • Batch processing: splitting a large delete or update into batches to control the log, locks and duration.
  • External effects: sending a message, calling a service, writing a file: the engine cannot group them.
  • Irreducible sequential logic: some computations where each step depends on a state computed by the previous one, with no reasonable windowed or recursive formulation.
  • Very small volumes: when readability matters most and the volume is bounded for certain.

But remember: an excellent block invoked inside a cursor loses its gains (see the approach, §5 and the measured cases).

-- Loop over OBJECTS: legitimate (dynamic SQL, one object per iteration)
DECLARE @sql nvarchar(max) =
    (SELECT STRING_AGG(CAST(N'ALTER INDEX ALL ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' REBUILD;' AS nvarchar(max)), CHAR(10))
     FROM   sys.tables AS t
     JOIN   sys.schemas AS s ON s.schema_id = t.schema_id);
-- PRINT @sql;                        -- review before executing
EXEC sys.sp_executesql @sql;
Rule of thumb

A loop that drives sets is orchestration. A loop that processes the rows of a set is, almost always, a design flaw.

Fake set-based code#

A query can look set-based and behave like a hidden loop:

  • Scalar functions called in the SELECT or the WHERE: executed per row, without parallelism, with a faulty cost estimate (partially fixed by inlining since SQL Server 2019, under conditions).
  • Multi-statement table-valued functions: limited row estimates and systematic materialization.
  • Correlated subqueries executed for each row of a large set, when a join or a window would suffice.
  • Recursive CTE used as a counter to generate series: recursion level by level, bounded by MAXRECURSION.
  • Disguised cursor: a WHILE loop that reads "the next row" with SELECT TOP (1) on each iteration.
  • Stored procedure calls per row, with a scalar parameter, over thousands of rows.
  • Triggers written as if only one row were processed.
-- Fake set-based: the scalar function is called for each row
SELECT CommandeId, dbo.fn_TauxRemise(ClientId) AS Taux FROM dbo.Commandes;

-- Set-based: inline table-valued function + APPLY, or a direct join
SELECT o.CommandeId, r.Taux
FROM   dbo.Commandes AS o
CROSS APPLY dbo.itvf_TauxRemise(o.ClientId) AS r;
-- Trigger written for one row: BUG as soon as a statement touches several rows
CREATE TRIGGER dbo.trg_Mauvais ON dbo.Commandes AFTER INSERT AS
BEGIN
    DECLARE @id int = (SELECT CommandeId FROM inserted);   -- fails or ignores rows if inserted contains several
    -- rest of the processing, based on @id
END;
GO

-- Correct version: treat inserted as a set
CREATE TRIGGER dbo.trg_Correct ON dbo.Commandes AFTER INSERT AS
BEGIN
    INSERT INTO dbo.Audit (CommandeId, DateAudit)
    SELECT CommandeId, SYSUTCDATETIME() FROM inserted;
END;

Safeguards#

  1. For each loop, ask yourself: what does it iterate over? Over objects or batches: probably legitimate. Over rows of data: needs justification.
  2. Write the set-based version first if it seems possible; only go back to the loop with a documented reason.
  3. Measure on realistic volumes (see Measuring seriously): neither intuition nor dogma replaces a measurement.
  4. Isolate the sequential part, and keep the rest set-based.
  5. Verify that the results are equal between the old and the new version before replacing.

Sources and verification#