Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 16 / 40

Common Table Expressions sul serio: le CTE ricorsive

Organigrammi, alberi di cartelle, distinte base e il generatore di dimensione date in una riga. Come ragionare sulle CTE ricorsive senza perdersi.

Nella lezione 11 abbiamo visto un esempio di CTE ricorsiva e siamo andati avanti. Oggi le dedichiamo l’intera lezione, perché la ricorsione in SQL è una di quelle funzionalità che, una volta capita, smetti per sempre di temere le query gerarchiche.

Runehold ha almeno tre dataset genuinamente ricorsivi:

  1. Organigramma. Ogni dipendente ha un manager, che ha un manager, fino al CEO.
  2. Categorie di prodotto. “Casa e arredo > Illuminazione > Lampade da terra > Lampade da terra a forma di pinguino.”
  3. Distinta base. Il bundle “Enchanted Starter Kit” contiene un libro di incantesimi, una tazza da viaggio e un canovaccio “moderatamente maledetto” — ognuno dei quali a sua volta potrebbe essere composto da SKU più semplici.

Ogni volta che la risposta alla domanda “e cosa c’è sotto questa cosa?” ha la stessa forma della domanda — hai un problema ricorsivo. Le CTE ricorsive sono il modo più pulito di esprimerlo.

La struttura in tre parti

Ogni CTE ricorsiva ha la stessa forma:

WITH my_cte AS (
    -- Parte 1: l'ANCHOR — da dove partiamo?
    SELECT ...
    FROM base_table
    WHERE seed_condition

    UNION ALL            -- obbligatorio, non UNION

    -- Parte 2: il MEMBRO RICORSIVO — come facciamo il passo successivo?
    SELECT ...
    FROM base_table AS t
    JOIN my_cte AS r ON r.col = t.col
)
-- Parte 3: la QUERY ESTERNA che legge la CTE
SELECT * FROM my_cte ORDER BY ...;

Tre parti. In ordine. UNION ALL obbligatorio; UNION farebbe silenziosamente la deduplica dei risultati intermedi e renderebbe la ricorsione sbagliata. Niente eccezioni.

SQL Server la esegue così:

  1. Esegui l’anchor. Metti il risultato in un set virtuale “current”.
  2. Esegui il membro ricorsivo, facendo join contro “current”. Il risultato è il nuovo “current”.
  3. Ripeti il punto 2 finché il membro ricorsivo non restituisce zero righe.
  4. Il risultato finale è l’unione di tutti i “current” prodotti.

Due regole che vengono gratis:

  • Il membro ricorsivo deve referenziare il nome della CTE (altrimenti non è ricorsivo).
  • Deve esserci una condizione di terminazione — altrimenti ricorri all’infinito. SQL Server di default mette un cap a 100 iterazioni; lo si sovrascrive con OPTION (MAXRECURSION n), 0 significa illimitato (pericoloso).

Esempio 1: l’organigramma

WITH org AS (
    -- Anchor: il CEO (nessun manager)
    SELECT EmployeeId,
           FullName,
           ManagerId,
           Team,
           0               AS level,
           CAST(FullName AS NVARCHAR(4000)) AS chain
    FROM HR.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    -- Ricorsivo: dipendenti il cui manager è già nella CTE
    SELECT e.EmployeeId,
           e.FullName,
           e.ManagerId,
           e.Team,
           o.level + 1,
           CAST(o.chain + N' > ' + e.FullName AS NVARCHAR(4000))
    FROM HR.Employee AS e
    JOIN org         AS o ON o.EmployeeId = e.ManagerId
)
SELECT level, REPLICATE(N'  ', level) + FullName AS indented_name, Team, chain
FROM org
ORDER BY chain;

L’output è un organigramma indentato:

CEO - Ilse Jansen
  VP Engineering - Tomasz Kowalski
    Eng Manager - Franz Hofmann
      Dev - Ada Lovelace
      Dev - Grace Hopper
    Eng Manager - Sofia Bianchi
  VP Marketing - Djenna Akkad
    ...

chain accumula il percorso dalla radice. Lo puoi usare per filtrare (“tutti quelli sotto VP Engineering”) o per la visualizzazione.

Attenzione alla lunghezza della catena. Fai il cast a un NVARCHAR(4000) grosso oppure la stringa potrebbe troncarsi alla lunghezza del primo valore dell’anchor. Classico tranello delle CTE ricorsive.

Esempio 2: categorie di prodotto

Stesso pattern, dati diversi:

-- Data Catalog.Category (CategoryId, ParentCategoryId, Name)
WITH tree AS (
    SELECT CategoryId, ParentCategoryId, Name, 0 AS depth,
           CAST(Name AS NVARCHAR(4000)) AS path
    FROM Catalog.Category
    WHERE ParentCategoryId IS NULL       -- categorie radice

    UNION ALL

    SELECT c.CategoryId, c.ParentCategoryId, c.Name, t.depth + 1,
           CAST(t.path + N' > ' + c.Name AS NVARCHAR(4000))
    FROM Catalog.Category AS c
    JOIN tree               AS t ON t.CategoryId = c.ParentCategoryId
)
SELECT depth, REPLICATE(N'    ', depth) + Name AS indented, path
FROM tree
ORDER BY path;

Ti dà l’intero albero delle categorie, ordinato in modo che i genitori vengano prima dei figli nell’output. Marketing lo ama per generare i menu di navigazione; ops lo ama per costruire report basati su categorie.

Esempio 3: distinta base

Prodotti composti da altri prodotti:

-- Data Catalog.Bom (BundleId, ComponentProductId, Quantity)
WITH bom AS (
    -- Anchor: il bundle di livello superiore
    SELECT BundleId AS product_id,
           ComponentProductId,
           Quantity,
           1 AS depth
    FROM Catalog.Bom
    WHERE BundleId = 12345           -- l'"Enchanted Starter Kit"

    UNION ALL

    -- Ricorsivo: componenti di componenti
    SELECT b.BundleId, b.ComponentProductId, b.Quantity * p.Quantity, p.depth + 1
    FROM Catalog.Bom AS b
    JOIN bom         AS p ON p.ComponentProductId = b.BundleId
)
SELECT ComponentProductId, SUM(Quantity) AS total_qty
FROM bom
GROUP BY ComponentProductId;

Espande un bundle fino in fondo e moltiplica le quantità lungo il cammino. “Lo Starter Kit contiene 1 Spellbook, e uno Spellbook contiene 3 Magical Bookmark, quindi il Kit di fatto contiene 3 Bookmark a livello foglia.”

Esempio 4: generare intervalli di date

Nessuna tabella necessaria. Genera date al volo:

-- Ogni giorno del Q1 2026
WITH days AS (
    SELECT CAST('2026-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM days WHERE d < '2026-03-31'
)
SELECT d, DATENAME(WEEKDAY, d) AS dow
FROM days
OPTION (MAXRECURSION 500);

Questo pattern è oro per i report con riempimento dei buchi. Il report vuole “fatturato per giorno negli ultimi 30 giorni, mostrando zero per i giorni senza ordini”? Fai LEFT JOIN della serie di date generata contro i tuoi dati aggregati:

WITH days AS (
    SELECT CAST(DATEADD(DAY, -29, GETDATE()) AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM days WHERE d < CAST(GETDATE() AS DATE)
),
daily_rev AS (
    SELECT CAST(OrderDate AS DATE) AS d, SUM(Total) AS rev
    FROM Sales.Orders
    WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())
    GROUP BY CAST(OrderDate AS DATE)
)
SELECT d.d, COALESCE(r.rev, 0) AS revenue_eur
FROM days AS d
LEFT JOIN daily_rev AS r ON r.d = d.d
ORDER BY d.d;

Trenta righe, una per giorno, zero dove non ci sono stati ordini. La tua dashboard non salta giorni. Marketing non può rivendicare “ma il 14 abbiamo fatto una campagna” e sentirsi rispondere “non c’è una riga per il 14 nel report.”

Il tranello di MAXRECURSION

SQL Server di default si ferma a 100 iterazioni. Per un organigramma profondo di solito basta. Per la generazione di date o l’attraversamento di grafi, no.

OPTION (MAXRECURSION 5000)   -- consenti fino a 5000 iterazioni
OPTION (MAXRECURSION 0)      -- illimitato (usa con cautela)

0 è “nessun limite” — pericoloso con una ricorsione involontariamente illimitata. Preferisci numeri espliciti; scegline uno comodamente sopra il tuo massimo reale e lascia che fallisca rumorosamente se qualcosa va storto.

L’hint OPTION va alla fine della SELECT più esterna, non dentro la CTE.

Attraversamento in ampiezza vs in profondità

La CTE ricorsiva di SQL Server è fondamentalmente breadth-first: produce tutte le righe di livello 1 prima di qualsiasi riga di livello 2. Se ti serve depth-first (tutti i discendenti prima dei fratelli), devi simularlo con l’ordinamento. Il trucco classico: costruisci un percorso ordinabile man mano che procedi, poi ORDER BY path:

WITH tree AS (
    SELECT CategoryId, ParentCategoryId, Name, 0 AS depth,
           CAST(RIGHT(REPLICATE('0', 10) + CAST(CategoryId AS VARCHAR(10)), 10) AS VARCHAR(4000)) AS sort_path
    FROM Catalog.Category
    WHERE ParentCategoryId IS NULL

    UNION ALL

    SELECT c.CategoryId, c.ParentCategoryId, c.Name, t.depth + 1,
           CAST(t.sort_path + '/' + RIGHT(REPLICATE('0', 10) + CAST(c.CategoryId AS VARCHAR(10)), 10) AS VARCHAR(4000))
    FROM Catalog.Category AS c
    JOIN tree             AS t ON t.CategoryId = c.ParentCategoryId
)
SELECT depth, REPLICATE('  ', depth) + Name AS indented
FROM tree
ORDER BY sort_path;

Gli ID con padding di zeri nel sort path assicurano che l’ordinamento alfabetico coincida con l’ordine numerico. ORDER BY sort_path ti dà un attraversamento in profondità.

Note sulle prestazioni

  • Le CTE ricorsive sono solitamente veloci quando il grafo è piccolo. Organigrammi di 200 dipendenti? Banale. Alberi di categorie con 500 nodi? Istantaneo.
  • Le CTE ricorsive possono essere lente su grafi grandi. Una camminata ricorsiva su un grafo da un milione di nodi farà male. Per problemi di grafo davvero grandi, le tabelle Graph di SQL Server (introdotte nel 2017) o un database di grafi dedicato sono meglio.
  • Il join tra il membro ricorsivo e la CTE viene eseguito una volta per iterazione. Se la tabella sottostante non ha un indice sulla colonna di join, ogni iterazione fa una scan. Per “dipendenti uniti su ManagerId” — assicurati di avere un indice su ManagerId.
  • Per la generazione profonda di date, una numbers-table o una date-dimension table dedicata è più veloce della ricorsione. Ma per report ad-hoc, il generatore di date ricorsivo è perfetto — nessun cambio di schema necessario.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Generatore di date + report con gap-fill
WITH days AS (
    SELECT CAST('2026-03-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM days WHERE d < '2026-04-30'
),
daily AS (
    SELECT CAST(OrderDate AS DATE) AS d, SUM(Total) AS rev
    FROM Sales.Orders
    GROUP BY CAST(OrderDate AS DATE)
)
SELECT d.d,
       COALESCE(daily.rev, 0) AS revenue_eur,
       DATENAME(WEEKDAY, d.d) AS dow
FROM days AS d
LEFT JOIN daily ON daily.d = d.d
ORDER BY d.d
OPTION (MAXRECURSION 500);

-- 2. Costruisci un piccolo organigramma per la demo
IF OBJECT_ID('HR.Employee', 'U') IS NULL
BEGIN
    IF SCHEMA_ID('HR') IS NULL EXEC('CREATE SCHEMA HR AUTHORIZATION dbo');
    CREATE TABLE HR.Employee (
        EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
        FullName   NVARCHAR(200) NOT NULL,
        Team       NVARCHAR(50)  NOT NULL,
        ManagerId  INT NULL,
        FOREIGN KEY (ManagerId) REFERENCES HR.Employee(EmployeeId)
    );

    INSERT INTO HR.Employee (FullName, Team, ManagerId) VALUES
    ('Ilse Jansen',     'Executive',  NULL);       -- 1, CEO
    INSERT INTO HR.Employee (FullName, Team, ManagerId) VALUES
    ('Tomasz Kowalski', 'Executive',  1),            -- 2, VP Eng
    ('Djenna Akkad',    'Executive',  1),            -- 3, VP Mktg
    ('Franz Hofmann',   'Engineering',2),            -- 4
    ('Sofia Bianchi',   'Engineering',2),            -- 5
    ('Ada Lovelace',    'Engineering',4),            -- 6
    ('Grace Hopper',    'Engineering',4),            -- 7
    ('Piotr Nowak',     'Marketing',  3);            -- 8
END;

-- 3. L'organigramma
WITH org AS (
    SELECT EmployeeId, FullName, ManagerId, Team, 0 AS lvl,
           CAST(FullName AS NVARCHAR(4000)) AS chain
    FROM HR.Employee WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.EmployeeId, e.FullName, e.ManagerId, e.Team, o.lvl + 1,
           CAST(o.chain + N' > ' + e.FullName AS NVARCHAR(4000))
    FROM HR.Employee AS e
    JOIN org         AS o ON o.EmployeeId = e.ManagerId
)
SELECT lvl, REPLICATE(N'    ', lvl) + FullName AS indented, Team, chain
FROM org
ORDER BY chain;

-- 4. Tutti sotto VP Engineering (filtra per chain)
WITH org AS (
    SELECT EmployeeId, FullName, ManagerId, 0 AS lvl
    FROM HR.Employee WHERE EmployeeId = 2   -- VP Eng

    UNION ALL

    SELECT e.EmployeeId, e.FullName, e.ManagerId, o.lvl + 1
    FROM HR.Employee AS e
    JOIN org         AS o ON o.EmployeeId = e.ManagerId
)
SELECT * FROM org WHERE lvl > 0;
-- Esclude il VP stesso; parte dai suoi riporti diretti.

Il modulo 2 è finito. Le query sono coperte da capo a piedi: SELECT, WHERE, NULL, ORDER BY, JOIN, GROUP BY, subquery, CTE, window, UNION, stringhe, date, ricorsione. Con questa cassetta degli attrezzi puoi rispondere al 95% delle domande di business che arrivano sulla scrivania di un data engineer in qualsiasi azienda di e-commerce europea.

Prossimo modulo: DML e transazioni. La lezione 17 inizia con la complicata storia d’amore di INSERT, UPDATE, DELETE, e perché MERGE è pericoloso.

Cerca