clement13.net
Original photo: Test tubes
Description
Test tubes
Source
B.Lachner · original page
Licence
CC0 Public domain
Original photo, unretouched. The background is a version of it stylised by the site.

Examples: procedural versus set-based

Six cases, side by side, with their pitfalls and their equivalence check.

≈ 2 min readReading
In brief
  • Six cases: running total, update, deduplication, aggregated text, calendar, islands.
  • Same structure: problem statement, data, two versions, pitfalls, verification.
  • Lookup table: "when a loop does X, think of Y".
On this page
  1. The six examples
  2. A lookup table
  3. Going further
  4. How to read these pages

Six common cases where row-by-row processing is tempting, each presented with the same structure: problem statement, data set, procedural version and set-based version side by side, expected result, pitfalls, variants and equivalence check.

Before you start

Each example is self-contained: create the tables in a test database, run the procedural version, then the set-based version, and compare. The last block of each page verifies that both versions return the same result. To quantify the differences, see Measuring seriously.

The six examples#

Running totalRunning total, moving average; the default frame pitfall.Window functions · 2012+Update by referenceFill a column from another table; determinism and NULL.UPDATE … FROM · OUTPUTDeduplicationKeep one row per business key; diagnosis, archiving, constraint.ROW_NUMBER · DELETE via CTEString aggregationConcatenate values per group; truncation, order, NULL.STRING_AGG · 2017+Generating a calendarProduce thousands of rows without a loop; DATEFIRST.Numbers table · GENERATE_SERIESContinuous ranges (islands)Detect sequences of consecutive days.ROW_NUMBER · LAG

A lookup table#

When a loop does X, look for the set-based equivalent of X:

If the loop does…Think of…Example
A running total or a current stateWindow function (SUM() OVER, LAG)Running total
A per-row lookup in another tableJoin (UPDATE … FROM … JOIN)Update by reference
An existence or duplicate testEXISTS, ROW_NUMBERDeduplication
A concatenation per groupSTRING_AGG (or FOR XML PATH)String aggregation
A row generationNumbers table, GENERATE_SERIESCalendar
Sequence tracking ("the previous row") Difference with ROW_NUMBER, or LAG + running sumIslands

Going further#

These six examples are patterns. For entire workloads that accumulate several errors, see the disaster cases, and the approach that avoids them.

How to read these pages#

  • The procedural code is written correctly: we compare valid versions, not a straw-man counterexample.
  • No performance figures are put forward: the gaps depend on the environment. The script on the Measuring seriously page lets you measure them on your own system.
  • The pitfalls are so many reasons to verify equivalence between the old and the new processing.