Een recursieve Common Table Expression (CTE) in SQL stelt je in staat om hiërarchische gegevens te doorlopen met behulp van een zelfrefererende query die op een set-gebaseerde manier wordt uitgevoerd. De structuur bestaat uit een ankerlid (de basisgeval, typisch wortelnodes waar manager_id IS NULL) en een recursief lid (het iteratieve deel dat de CTE terug met zichzelf verbindt op de ouder-kind relatie). De database-engine voert het recursieve lid herhaaldelijk uit totdat er geen nieuwe rijen worden geretourneerd, waarbij de resultaten incrementeel worden opgebouwd zonder expliciete iteratieconstructies.
Deze aanpak maakt gebruik van de declaratieve aard van SQL, waardoor de optimizer efficiënte join-algoritmen kan kiezen (typisch hash- of merge-joins) in plaats van de rij-voor-rij verwerking die inherent is aan CURSOR of WHILE lussen. De syntaxis gebruikt WITH RECURSIVE in PostgreSQL en MySQL, of simpelweg WITH in SQL Server (waar recursie impliciet is), gevolgd door de naam van de CTE en de kolomlijst.
Een multinationale onderneming met 12.000 werknemers moest volledige rapportageketens genereren voor SOX-nalevingsaudits. Het bestaande systeem gebruikte een T-SQL CURSOR die door elke werknemer iteriseerde, een schaalbare functie aanriep om hun manager te vinden, en de hiërarchie string voor string opbouwde. Dit proces duurde 47 minuten om te voltooien, hield vergrendelingen op de Employees-tabel die HR-updates tijdens kantooruren verhinderden, en faalde vaak met stack overflow-fouten bij het verwerken van diepe hiërarchieën van meer dan 100 niveaus (gebruikelijk in de matrixstructuur van de engineeringafdeling).
Oplossing A: Geoptimaliseerde CURSOR met tijdelijke tabellen. Het team overwoog om de cursor opnieuw te schrijven om resultaten eerst in een tijdelijke tabel te dumpen en vervolgens aan het eind bulk in te voegen. Dit zou de vergrendelingstijd verminderen van 47 minuten naar ongeveer 40 minuten. Voordelen: minimale codewijzigingen, bekend patroon voor het legacy-team. Nadelen: nog steeds rij-voor-rij verwerking, nog steeds kwetsbaar voor diepe recursiestackoverflows, slechts mitigatie in plaats van oplossing van het prestatieprobleem.
Oplossing B: HierarchyID gegevenstype. Het migreren van de tabel om het SQL Server's native HierarchyID type te gebruiken, dat boomposities opslaat als gecodeerde paden die zijn geoptimaliseerd voor doorlopen. Voordelen: O(1) subboomophaling, ingebouwde methoden zoals GetAncestor() en GetDescendant(), extreem snel voor leesintensievere werkbelastingen. Nadelen: vereist massale schema-migratie (12.000 rijen plus historische gegevens), complex om te onderhouden tijdens reorganisaties (het bijwerken van een manager vereist het herberekenen van alle afgeleiden paden), vendor-locked naar SQL Server terwijl het bedrijf een migratie naar PostgreSQL overwoog.
Oplossing C: Recursieve CTE met cycledetectie. Implementeren van een recursieve CTE die de werknemers tabel met zichzelf verbindt op manager_id, met behulp van een padarray om bezochte knopen bij te houden om oneindige lussen te voorkomen in geval van cirkelreferenties (wat twee keer was gebeurd door gegevensinvoeringsfouten). Voordelen: pure ANSI SQL standaard (portabel naar PostgreSQL tijdens migratie), set-gebaseerde uitvoering verminderde de looptijd tot 4 minuten en 12 seconden, geen tabelvergrendelingen gehouden tijdens uitvoering (maakt gebruik van snapshot-isolatie), kan willekeurige dieptes aan zonder stack overflow, detecteert automatisch datakwaliteitsproblemen (cycli).
Het team koos voor Oplossing C. De implementatie gebruikte een recursieve CTE met een path kolom die werknemers-ID's ophoopte met behulp van PostgreSQL's array-aggregatie (of stringconcatenatie in SQL Server), met een WHERE-clausule die controleert dat de nieuwe manager_id zich niet in het opgehoopte pad bevond. Het resultaat was een prestatieverbetering van 91%, eliminatie van productievergrendelingen, en vroege detectie van circulaire rapportagerelaties die voorheen applicatiecrashes veroorzaakten.
Waarom beëindigt een recursieve CTE, en wat gebeurt er als de gegevens een cirkelreferentie bevatten?
Kandidaten geloven vaak dat recursieve CTE's ingebouwde cycledetectie hebben, maar standaard SQL-recursie eindigt alleen wanneer het recursieve lid nul nieuwe rijen retourneert. Als Werknemer A rapporteert aan B, B aan C, en C terug aan A, draait de CTE eindeloos (of totdat deze de implementatielimieten zoals SQL Server's standaard 100 recursieniveaus bereikt). De oplossing vereist handmatige cycledetectie door bezochte knoop-ID's in een padkolom op te accumuleren (met behulp van arrays of gescheiden strings) en te filteren WHERE new_id != ALL(path_array). Moderne PostgreSQL (14+) en SQL Server (2022+) ondersteunen de standaard SQL:1999 CYCLE clausule: WITH RECURSIVE cte AS (...) CYCLE id SET is_cycle USING path, wat dit automatisch afhandelt.
Hoe verschilt het uitvoeringsplan tussen een recursieve CTE en een cursor-gebaseerde aanpak, en waarom is dit belangrijk voor concurrentie?
Junior kandidaten beweren vaak dat CTE's "sneller" zijn zonder het uitvoeringsmodel te begrijpen. Een CURSOR in SQL Server of PostgreSQL dwingt de engine om een resultaatsset te materialiseren en rij-voor-rij te itereren, doorgaans met een Keyset of Static cursor type dat vergrendelingen of tempdb-resources vereist voor de duur van de iteratie. Dit creëert Shared Locks (of Update Locks) op de onderliggende tabellen gedurende de volledige 47 minuten in het bovenstaande voorbeeld. Daarentegen is een recursieve CTE een enkele SELECT instructie. Onder Read Committed Snapshot Isolation (RCSI) of Snapshot Isolation, leest het een consistente weergave van de gegevens zonder vergrendelingen vast te houden, waarbij de versieopslag wordt gebruikt in plaats daarvan. De optimizer kiest doorgaans voor Nested Loop Joins voor het recursieve lid met Index Seek bewerkingen op de ouder-kind index, waardoor het O(n log n) maakt in plaats van O(n²) voor cursorbenaderingen.
Wat is het verschil tussen een recursieve CTE en het Nested Sets Model voor hiërarchische gegevens, en wanneer zou je de ene boven de andere kiezen?
Kandidaten verwarren vaak doorloopmethoden met opslagmodellen. Een recursieve CTE is een query-tijd doorlooptechniek die werkt op Adjacency Lists (foreign keys van parent_id). Het Nested Sets Model (linker/rechter waarden) is een opslageontwerppatroon dat vooraf boomdoorlooppaden berekent. Voor schrijf-intensievere werkbelastingen (frequente manager wijzigingen), zijn recursieve CTE's op adjacency lists superieur omdat het bijwerken van een enkele parent_id O(1) is, terwijl geneste sets O(n) bijwerkingen vereisen voor alle rechterwaarden rechts van de verplaatste knoop. Voor lees-intensievere, statische hiërarchieën (org-diagrammen die maandelijks wijzigen) bieden geneste sets O(1) subboomophaling (WHERE left BETWEEN parent.left AND parent.right) versus O(n) voor recursieve CTE's. Een hybride aanpak gebruikt Closure Tables (een aparte tabel om alle voorouder-afgeleiden paren op te slaan), die O(1) biedt voor zowel doorlopen als het vinden van alle kinderen, ten koste van O(n²) opslag en complexere onderhoud. De keuze hangt af van de lees/schrijf-verhouding: gebruik recursieve CTE's wanneer schrijft > 5% van de bewerkingen; gebruik geneste sets of closure tables voor overwegend statische bomen.