i

Description

A data center seen from the roof

Source

Rsparks3 · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Set-based programming in T-SQL

Describe the result, not the path: the way of thinking that changes the scale of what SQL can handle.

In brief

• Goal: express the expected result and let the optimizer choose the execution.
• Principle: sets as input, one set as output, composed with one another.
• Tools: joins, aggregates, window functions, CTEs, APPLY, set-based writes.
• Safeguard: a loop over objects is legitimate; over data rows, rarely.

On this page

1. Goal
2. The principle in five ideas
3. Procedural and set-based at a glance
4. A minimal example
   1. Procedural
   2. Set-based
5. Outline of this section
6. Sources and verification

Goal#

Set-based programming means expressing the result you want (a set of rows defined by properties), rather than the sequence of steps that builds it row after row. In T-SQL, this amounts to entrusting the query optimizer with the choice of execution method.

Its benefits, when practiced well:

• Performance: one statement processes thousands of rows in a single pass, with parallelism, suitable join algorithms and batched writes.
• Readability: the intent (the “what”) is visible; the mechanics (the “how”) are left to the engine.
• Reliability: a statement is atomic; no intermediate state to manage by hand.
• Maintainability: fewer variables, fewer branches, fewer side effects.
• Scalability: cost most often grows almost linearly with volume, instead of exploding.

The principle in five ideas#

1. A table is a set of rows, with no order. No row is “the first” until a sort has been requested.
2. Each operation takes sets and returns a set. They can therefore be composed: filter, join, group, then reuse the result.
3. You describe the property of the result (“orders more than two years old”), not the algorithm to find them.
4. The optimizer chooses the execution plan based on statistics and indexes.
5. You think in terms of “grain”: what does one row of the result correspond to (a sale, a customer per month, a day)? Almost all errors come from a poorly defined grain.

Procedural and set-based at a glance#

A minimal example#

Mark as archived the orders prior to 2020:

-- Procedural: mark old orders, one by one
DECLARE @Id int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT CommandeId FROM dbo.Commandes WHERE DateCommande < '2020-01-01';
OPEN c;
FETCH NEXT FROM c INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Commandes SET Statut = 'ARCHIVEE' WHERE CommandeId = @Id;
    FETCH NEXT FROM c INTO @Id;
END;
CLOSE c;
DEALLOCATE c;

-- Set-based: a single statement, a single plan
UPDATE dbo.Commandes
SET    Statut = 'ARCHIVEE'
WHERE  DateCommande < '2020-01-01';

The second form expresses the same thing in one statement. The engine can use an index on DateCommande, batch the writes to the transaction log and, if the volume justifies it, parallelize. The first form executes as many statements as there are rows: at each iteration, a row is read, a statement is launched, a row is modified.

Outline of this section#

Sources and verification#

• Codd, E. F. (1970), “A Relational Model of Data for Large Shared Data Banks”, CACM 13(6), 377–387 — foundation of the relational model
• Microsoft Learn — OVER Clause (Transact-SQL) — window functions
• Microsoft Learn — Scalar UDF Inlining — cost of scalar functions, SQL Server 2019 improvements