i

Description

Un établi en bois avec un compas à pointes sèches, un carnet de cuir et un pied de lampe en tuyau.

Source

Jeff Sheldon (ugmonk) · original page

Licence

CC0 Public domain

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

T-SQL tools

The T-SQL constructs that let you stay set-based, with their minimum version.

In brief

• Read: joins, EXISTS, aggregates, windows, CTEs, APPLY.
• Write: INSERT…SELECT, UPDATE/DELETE…FROM, MERGE (caution), OUTPUT.
• Generate: GENERATE_SERIES or numbers table.
• Pitfall: default window frame is RANGE.

On this page

1. Overview of features and their versions
2. Joins, semi-joins and anti-joins
3. Aggregates and groupings
4. Window functions
5. Table expressions (CTEs)
6. APPLY
7. Set operators
8. Set-based writes
9. Table types and table-valued parameters
10. Series and numbers tables
11. Functions: inline or scalar?
12. Indexes and statistics
13. Observing
14. Batch processing
15. Sources and verification

Overview of features and their versions#

The versions shown are those of first availability in SQL Server (and, unless stated otherwise, in Azure SQL Database).

Joins, semi-joins and anti-joins#

A join brings two sets together. When you only want to test for the existence of a match, EXISTS expresses the intent without multiplying rows (semi-join); NOT EXISTS expresses absence (anti-join) and remains correct in the presence of NULLs, unlike NOT IN.

-- Semi-join: customers with at least one order
SELECT c.ClientId FROM dbo.Clients AS c
WHERE EXISTS (SELECT 1 FROM dbo.Commandes AS o WHERE o.ClientId = c.ClientId);

-- Anti-join: customers without an order (NULL-safe)
SELECT c.ClientId FROM dbo.Clients AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.Commandes AS o WHERE o.ClientId = c.ClientId);

Aggregates and groupings#

GROUP BY reduces a set to a set of groups. GROUPING SETS, ROLLUP and CUBE compute several levels of grouping in a single pass.

SELECT ClientId, YEAR(DateCommande) AS Annee, SUM(Montant) AS Total
FROM   dbo.Commandes
GROUP BY ROLLUP (ClientId, YEAR(DateCommande));   -- subtotals per customer and grand total

Window functions#

A window function computes a value for each row without reducing the set: ranking, previous or next value, running totals, moving averages. Three elements define it: PARTITION BY (partitioning), ORDER BY (logical order), and the ROWS/RANGE frame.

SELECT ClientId, DateCommande, Montant,
       ROW_NUMBER() OVER (PARTITION BY ClientId ORDER BY DateCommande, CommandeId) AS Rang,
       LAG(Montant)  OVER (PARTITION BY ClientId ORDER BY DateCommande, CommandeId) AS MontantPrecedent,
       SUM(Montant)  OVER (PARTITION BY ClientId ORDER BY DateCommande, CommandeId
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)           AS Cumul
FROM   dbo.Commandes;

Table expressions (CTEs)#

A CTE names a subset for readability. It is not materialized by design: it is a wrapper around the query, which the optimizer may rewrite. The recursive CTE is used for hierarchies and graphs; it is bounded by MAXRECURSION (100 by default).

-- Recursive CTE: traversing a hierarchy
WITH Arbre AS (
    SELECT NoeudId, ParentId, 0 AS Niveau FROM dbo.Noeuds WHERE ParentId IS NULL
    UNION ALL
    SELECT n.NoeudId, n.ParentId, a.Niveau + 1
    FROM   dbo.Noeuds AS n
    JOIN   Arbre AS a ON a.NoeudId = n.ParentId
)
SELECT NoeudId, ParentId, Niveau FROM Arbre
OPTION (MAXRECURSION 100);

APPLY#

CROSS APPLY / OUTER APPLY evaluate a table expression for each row of the left-hand set, which enables "top N per group" and value unnesting. Used with an inline table-valued function or a subquery, it remains set-based: the optimizer rewrites it as a join when possible.

-- The 3 most recent orders of each customer
SELECT c.ClientId, d.CommandeId, d.DateCommande
FROM   dbo.Clients AS c
CROSS APPLY (SELECT TOP (3) o.CommandeId, o.DateCommande
             FROM dbo.Commandes AS o
             WHERE o.ClientId = c.ClientId
             ORDER BY o.DateCommande DESC, o.CommandeId DESC) AS d;

Set operators#

UNION ALL concatenates (with no deduplication cost), UNION deduplicates, INTERSECT keeps the common rows, EXCEPT removes the rows of the second set. EXCEPT in both directions is the simplest way to compare two results.

Set-based writes#

• INSERT … SELECT loads a set in a single statement.
• UPDATE … FROM … JOIN and DELETE … FROM … JOIN modify based on another table (watch determinism, see principles).
• MERGE combines insert, update and delete; several known issues call for caution and for considering separate statements.
• OUTPUT returns or records the affected rows (before/after): auditing without a second pass.

-- Set-based write with change tracking
UPDATE c
SET    c.TauxRemise = r.Taux
OUTPUT inserted.CommandeId, deleted.TauxRemise AS Ancien, inserted.TauxRemise AS Nouveau
INTO   dbo.Audit_Remises (CommandeId, Ancien, Nouveau)
FROM   dbo.Commandes AS c
JOIN   dbo.Remises  AS r ON r.CategorieId = c.CategorieId;

Table types and table-valued parameters#

A table type (CREATE TYPE … AS TABLE, SQL Server 2008) lets you pass a set of rows to a procedure or function (READONLY parameter). The caller freezes the set; the routine processes it without recomputing it, shielded from other transactions. Details, demonstration and limits: Determinism, §7.

Series and numbers tables#

Generating sequences (calendars, ranges, test data sets) without a loop:

-- Number series without a loop
-- SQL Server 2022 and Azure SQL:
SELECT value AS n FROM GENERATE_SERIES(0, 99);

-- Before 2022: numbers table by Cartesian product
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b;

Functions: inline or scalar?#

Inline table-valued functions (a single RETURN SELECT … statement) are expanded into the calling query, like a parameterized view: they remain set-based. Scalar functions are, historically, executed row by row, without parallelism or correct cost estimation. SQL Server 2019 (compatibility level 150) can inline some of them under conditions; this is neither systematic nor retroactive. Multi-statement table-valued functions also have limited cardinality estimates. See also determinism of functions and code generation and inlining.

Indexes and statistics#

The set-based approach does not remove the need for indexes: it makes them useful. For a window function, an index whose key columns follow PARTITION BY then ORDER BY avoids an expensive sort. Up-to-date statistics are needed for good estimates. A columnstore index or batch mode (SQL Server 2019, compatibility level 150) can speed up window operators on large volumes.

Observing#

• The actual execution plan: operators, estimates versus reality, warnings (spills, implicit conversions).
• SET STATISTICS IO, TIME ON: logical reads and CPU time.
• Query Store (SQL Server 2016+): history of plans and their performance.

See Measuring seriously.

Batch processing#

When a workload has to be split up (to limit the log, locks, or the duration of a transaction), you loop over batches of sets, not over rows. The loop is then a scheduling mechanism; each iteration remains set-based.

-- Batch processing: a loop, but over SETS of 50,000 rows
DECLARE @n int = 1;
WHILE @n > 0
BEGIN
    DELETE TOP (50000) FROM dbo.Journal
    WHERE  DateEvenement < DATEADD(YEAR, -2, SYSUTCDATETIME());
    SET @n = @@ROWCOUNT;
END;

Sources and verification#

• Microsoft Learn — OVER clause — default frame, ROWS/RANGE (SQL Server 2012+), performance advice
• Microsoft Learn — STRING_AGG — SQL Server 2017+
• Microsoft Learn — Scalar UDF Inlining — SQL Server 2019, compatibility level 150
• Microsoft Learn — Transact-SQL reference — official documentation; check availability by version