SQL CHIT CHAT … Blog a Sql Serverről

Összefoglaló

A közös táblakifejezések az SQL Server 2005-ben kerültek bevezetésre. Ezek az Sql Serverben elérhető táblakifejezések több típusának egyikét képviselik. A rekurzív CTE egy olyan CTE típus, amely önmagára hivatkozik. Általában hierarchiák feloldására használják.
Ebben a bejegyzésben megpróbálom elmagyarázni, hogyan működik a CTE rekurzió, hol helyezkedik el az Sql Serverben elérhető táblakifejezések csoportjában, és néhány olyan esetet mutatok be, ahol a rekurzió ragyog.

Táblakifejezések

A táblakifejezés egy megnevezett lekérdezési kifejezés, amely egy relációs táblát reprezentál. Az Sql Server négyféle táblakifejezést támogat;

• Derived tables
• Views
• ITVF (Inline Table Valued Functions aka paraméterezett nézetek)
• CTE (Common Table Expressions)
  • Rekurzív CTE

A táblakifejezések általában nem materializálódnak a lemezen. Ezek virtuális táblák, amelyek csak a RAM memóriában vannak jelen (a lemezre kerülhetnek pl. a memóriaterhelés, a virtuális tábla mérete stb… következtében). A táblázatkifejezések láthatósága eltérő lehet, pl. a nézetek és az ITVF adatbázis-szinten látható db objektumok, míg ezek hatóköre mindig az SQL utasítás szintjén van – a táblázatkifejezések egy kötegen belül nem működhetnek különböző SQL utasítások között.

A táblázatkifejezések előnyei nem a lekérdezés végrehajtásának teljesítményéhez, hanem a kód logikai aspektusához kapcsolódnak !

A származtatott táblák

A származtatott táblák a táblázatkifejezések, más néven al-lekérdezések. A kifejezéseket egy külső lekérdezés FROM záradékában definiáljuk. A származtatott táblák hatóköre mindig a külső lekérdezés.

A következő kód egy AUSCust nevű származtatott táblát reprezentál.

Tranzak-SQL

A származtatott AUSCust tábla csak a külső lekérdezés számára látható, és hatóköre az sql utasításra korlátozódik.

Nézet

A nézetek (néha virtuális kapcsolatoknak is nevezik) újrafelhasználható táblakifejezések. A nézetdefiníciót Sql Server objektumként tárolják az olyan objektumokkal együtt, mint például: felhasználó által definiált táblák, triggerek, függvények, tárolt eljárások stb.
A nézetek fő előnye más típusú táblázatkifejezésekkel szemben az újrafelhasználhatóság, azaz a származtatott lekérdezések és a CTE-k hatóköre egyetlen utasításra korlátozódik.
A nézetek nem materializálódnak, ami azt jelenti, hogy a nézetek által előállított sorok nem tárolódnak tartósan a lemezen. Az indexelt nézetek az Sql Server (hasonlóak, de nem azonosak a materializált nézetekkel más adatbázisplatformokon) a nézetek speciális típusai, amelyek eredménykészlete tartósan tárolható a lemezen – az indexelt nézetekről bővebben itt olvashat.

Mindössze néhány alapvető irányelv az SQL nézetek definiálásához.

• SELECT * egy nézetdefiníció keretében másképp viselkedik, mint amikor egy kötegben lekérdezési elemként használják.

  Transact.SQL

  1 2 3 4 5

  CREATE VIEW dbo.vwTest AS SELECT * FROM dbo.T1 …

  A nézetdefiníció a nézet létrehozásakor tartalmazza az összes oszlopot az alapul szolgáló dbo.T1 táblából. Ez azt jelenti, hogy ha megváltoztatjuk a tábla sémáját (azaz oszlopokat adunk hozzá és/vagy távolítunk el), a változások nem lesznek láthatóak a nézetben – a nézetdefiníció nem fog automatikusan megváltozni, hogy támogassa a tábla változásait. Ez hibákat okozhat azokban a helyzetekben, amikor pl. egy nézet nem létező oszlopokat próbál kiválasztani a mögöttes táblából.
  A probléma megoldásához a két rendszereljárás egyikét használhatjuk: sys.sp_refreshview vagy sys.sp_refreshsqlmodule.
  Az ilyen viselkedés elkerülése érdekében kövesse a legjobb gyakorlatot, és a nézet definíciójában kifejezetten nevezze meg az oszlopokat.

• A nézetek táblázatos kifejezések, ezért nem rendezhetők. A nézetek nem kurzorok! Lehetséges azonban “visszaélni” a TOP/ORDER BY konstrukcióval a nézet definíciójában, hogy megpróbáljuk kikényszeríteni a rendezett kimenetet. pl. .

  Tranzak-SQL

  1 2 3 4 5

  CREATE VIEW dbo.MyCursorView AS SELECT TOP(100 PERCENT) * FROM dbo.SomeTable ORDER BY column1 DESC

  A keresőoptimalizáló elveti a TOP/ORDER BY-t, mivel a táblázatos kifejezés eredménye mindig egy táblázat – a TOP(100 PERCENT) kiválasztásának amúgy sincs értelme. A táblaszerkezetek mögötti ötlet a relációs adatbázis-elmélet egyik fogalmából, a Relációból származik.

• A nézetre hivatkozó lekérdezés feldolgozása során a nézetdefinícióból származó lekérdezés kibontásra vagy kibővítésre kerül, és a fő lekérdezés kontextusában kerül végrehajtásra. Az összevont kód(lekérdezés) ezután optimalizálásra és végrehajtásra kerül.

ITVF (Inline Table Valued Functions)

AzITVF-ek olyan újrafelhasználható táblázatos kifejezések, amelyek támogatják a bemeneti paramétereket. A függvények paraméterezett nézetekként kezelhetők.

CTE (Common Table Expressions)

A Common Table Expressions hasonlóak a származtatott táblázatokhoz, de számos fontos előnnyel rendelkeznek;

A CTE-t egy WITH utasítással definiáljuk, amelyet egy táblázatos kifejezés definíciója követ. A félreérthetőség elkerülése érdekében (a TSQL a WITH kulcsszót más célokra használja, pl. WITH ENCRYPTION stb.) a CTE WITH záradékát megelőző utasítást egy féloszloppal KELL lezárni. Erre nincs szükség, ha a WITH záradék a legelső utasítás egy kötegben, azaz egy VIEW/ITVF definícióban)

MEGJEGYZÉS: A féloszlopot, az utasításvégzőt az ANSI szabvány támogatja, és a TSQL programozási gyakorlat részeként erősen ajánlott használni.

Rekurzív CTE

A SQL Server a 2005-ös verzió (Yukon) óta támogatja a rekurzív CTE-ken keresztül megvalósított rekurzív lekérdezési lehetőségeket.

A rekurzív CTE elemei

1. Horgonytag(ok) – Olyan lekérdezésdefiníciók, amelyek;
   1. érvényes relációs eredménytáblát adnak vissza
   2. a lekérdezés végrehajtásának kezdetén CSAK EGYSZER hajtódik végre
   3. mindig az első rekurzív tagdefiníció előtt helyezkedik el
   4. az utolsó horgonytagot UNION ALL operátornak kell követnie. Az operátor egyesíti az utolsó horgonytagot az első rekurzív taggal
2. UNION ALL többhalmazú operátor. Az operátor
3. rekurzív tag(ok)on működik – Olyan lekérdezésdefiníciók, amelyek;
   1. érvényes relációs eredménytáblát adnak vissza
   2. hivatkozással rendelkeznek a CTE névre. A CTE névre való hivatkozás logikailag az előző eredményhalmazt jelenti egy végrehajtási sorozatban. azaz A sorozatban az első “előző” eredményhalmaz az az eredmény, amelyet a horgonytag visszaadott.
4. CTE meghívás – A rekurziót meghívó végső utasítás
5. Hibabiztos mechanizmus – A MAXRECURSION opció megakadályozza az adatbázis rendszerét a végtelen ciklusokban. Ez egy opcionális elem.

Termination check

A CTE rekurzív tagjának nincs explicit rekurzió-megszüntetési ellenőrzése.
Sok programozási nyelvben tervezhetünk önmagát hívó metódust – rekurzív metódust. Minden rekurzív metódusnak meg kell szűnnie, ha bizonyos feltételek teljesülnek. Ez az explicit rekurzió-megszüntetés. Ezt követően a metódus elkezd értékeket visszaadni. Megszüntetési pont nélkül a rekurzió a végén “végtelenül” hívhatja magát.
A CTE rekurzív tagok megszüntetésének ellenőrzése implicit , ami azt jelenti, hogy a rekurzió akkor áll le, ha az előző CTE végrehajtásból nem érkezik vissza sor.

Itt egy klasszikus példa a rekurzióra az imperatív programozásban. Az alábbi kód egy egész szám faktoriálisát számítja ki egy rekurzív függvény(metódus) hívás segítségével.

A teljes konzolos programkód itt található.

MAXRECURSION

Mint már említettük, a rekurzív CTE-k, valamint bármely rekurzív művelet végtelen ciklusokat okozhat, ha nem megfelelően tervezik meg. Ez a helyzet negatív hatással lehet az adatbázis teljesítményére. Az Sql Server motorja rendelkezik egy hibabiztos mechanizmussal, amely nem engedi meg a végtelen végrehajtásokat.

Alapértelmezés szerint a rekurzív tag meghívásának száma 100-ra van korlátozva (ez nem számít bele az egyszeri lehorgonyzott végrehajtás). A kód a rekurzív tag 101. végrehajtásakor sikertelen lesz.

Msg 530, Level 16, State 1, Line xxx
The statement terminated. A maximális 100 rekurzió kimerült az utasítás befejezése előtt.

A rekurziók számát a MAXRECURSION n lekérdezési opció kezeli. Az opció felülbírálhatja a maximálisan megengedett rekurziók alapértelmezett számát. Az (n) paraméter a rekurziós szintet jelöli. 0<=n <=32767

Fontos megjegyzés:: MAXRECURSION 0 – kikapcsolja a rekurziós korlátot!

Az 1. ábra egy rekurzív CTE példáját mutatja az elemeivel

1. ábra, Rekurzív CTE elemek

A deklaratív rekurzió egészen más, mint a hagyományos, imperatív rekurzió. Az eltérő kódszerkezet mellett megfigyelhetjük az explicit és az implicit terminációs ellenőrzés közötti különbséget. A CalculateFactorial példában az explicit terminációs pontot egyértelműen a feltétel határozza meg: if (number == 0) then return 1.
A fenti rekurzív CTE esetében a terminációs pontot implicit módon az INNER JOIN művelet határozza meg, pontosabban annak ON záradékában szereplő logikai kifejezés eredménye: ON e.MgrId = c.EmpId. A táblázatművelet eredménye határozza meg a rekurziók számát. Ez a következő szakaszokban világosabbá válik.

Rekurzív CTE használata az alkalmazottak hierarchiájának feloldására

Sok forgatókönyv van, amikor rekurzív CTE-ket használhatunk, pl. elemek szétválasztására stb. A leggyakoribb forgatókönyv, amellyel a sokéves szekvenálás során találkoztam, az volt, hogy rekurzív CTE-t használjunk különböző hierarchikus problémák megoldására.

A dolgozói fa hierarchia klasszikus példája egy hierarchikus problémának, amelyet rekurzív CTE-kkel lehet megoldani.

Példa

Tegyük fel, hogy van egy szervezetünk 12 alkalmazottal. A következő üzleti szabályok érvényesek;

• Egy alkalmazottnak egyedi azonosítóval, EmpId-vel
  • kell rendelkeznie:
• Egy alkalmazottat 0 vagy 1 menedzser irányíthat.
  • Egy alkalmazottat 0 vagy 1 menedzser irányíthat.
    • Kényszeríti: Primary Key constraint on EmpId column: PK on EmpId, FK on MgrId and NULLable MgrId column
• Egy vezető egy vagy több alkalmazottat irányíthat.
  • enforced by: MgrId oszlopon
• Egy vezető nem irányíthatja saját magát.
  • megerősíti: Foreign Key constraint(self referenced) on MgrId column
• enforced by: MgrId oszlopon

A fahierarchia a dbo.Employees nevű táblában van megvalósítva. A szkriptek itt találhatók.

2. ábra, Employees tábla

A kérdés megválaszolásával mutassuk be a rekurzív CTE működésének módját: A 2. ábrán látható hierarchiafából jól látható, hogy a Manager (EmpId = 3) közvetlenül irányítja az alkalmazottakat; EmpId=7, EmpId=8 és EmpId=9 és közvetve irányítja; EmpId=10, EmpId=11 és EmpId=12.

A 3. ábra az EmpId=3 hierarchiát és a várt eredményt mutatja. A kód itt található.

3. ábra, EmpId=3 közvetlen és közvetett alárendeltek

Hogyan kaptuk meg a végeredményt.

A rekurzív rész az aktuális iterációban mindig az előző iterációban kapott eredményére hivatkozik. Az eredmény egy cte1 (az INNER JOIN jobb oldalán lévő tábla) nevű táblázat kifejezés(vagy virtuális tábla). Mint láthatjuk, a cte1 tartalmazza a horgonyzó részt is. A legelső futtatásban(az első iterációban) a rekurzív rész nem tud hivatkozni az előző eredményére, mert nem volt előző iteráció. Ezért az első iterációban csak a horgonyzó rész hajtódik végre, és az egész folyamat során csak egyszer. A második iterációban a lehorgonyzott lekérdezés eredményhalmaza adja a rekurzív rész korábbi eredményét. A horgony úgy működik, mint egy lendkerék, ha úgy tetszik 🙂

A végeredmény az iterációkon keresztül épül fel, azaz Horgony eredménye + 1. iteráció eredménye + 2. iteráció eredménye …

A logikai végrehajtási sorrend

A tesztlekérdezés végrehajtása az alábbi logikai sorrend szerint történik:

1. A cte1 kifejezésen kívüli SELECT utasítás hívja elő a rekurziót. A lehorgonyzó lekérdezés végrehajtódik, és visszaad egy cte1 nevű virtuális táblát. A rekurzív rész egy üres táblát ad vissza, mivel nincs meg az előző eredménye. Ne feledjük, hogy a halmazalapú megközelítésben a kifejezések egyszerre kerülnek kiértékelésre.
   4. ábra, cte1 értéke az 1. iteráció után
2. A második iteráció megkezdődik.Ez az első rekurzió. A horgonyzó rész az első iterációban játszotta a szerepét, és innentől kezdve csak üres halmazokat ad vissza. A rekurzív rész azonban most már hivatkozhat a korábbi eredményére (cte1 érték az első iteráció után) az INNER JOIN operátorban. A táblázatos művelet a második iteráció eredményét az alábbi ábrán látható módon állítja elő.
   5. ábra, cte1 érték a 2. iteráció után
3. A második iteráció nem üres halmazt eredményez, így a folyamat a harmadik iterációval – a második rekurzióval – folytatódik. A rekurzív elem most a második iteráció cte1 eredményére hivatkozik.
   
   6. ábra, cte1 érték a 3. iteráció után
4. A 4. iterációban – a harmadik rekurziós kísérletben – érdekes dolog történik. Az előző mintát követve a rekurzív elem az előző iteráció cte1 eredményét használja. Ezúttal azonban nincsenek az INNER JOIN művelet eredményeként visszaadott sorok, és a rekurzív elem üres halmazt ad vissza. Ez a korábban említett implicit befejezési pont. Ebben az esetben az INNER JOIN logikai kifejezés kiértékelése diktálja a rekurziók számát.
   Mivel az utolsó cte1 eredmény egy üres eredményhalmaz, a 4. iteráció (vagy 3. rekurzió) “törlődik” és a folyamat sikeresen befejeződik.
   
   7. ábra, Az utolsó iteráció
   A 3. rekurzió logikai törlése (az utolsó rekurzió, amely üres eredményhalmazt eredményezett, nem számít) a következő, rekurzív CTE végrehajtási terv elemzésére vonatkozó részben válik érthetőbbé.A lekérdezés végére hozzáadhatjuk az OPTION(MAXRECURSION 2) lekérdezési opciót, amely a megengedett rekurziók számát 2-re korlátozza. A lekérdezés helyes eredményt fog produkálni, bizonyítva, hogy ehhez a feladathoz csak két rekurzióra van szükség.Megjegyzés: A fizikai végrehajtás szempontjából az eredményhalmaz fokozatosan (sorok felbuborékolásával) kerül elküldésre a hálózati pufferekbe és vissza az ügyfélalkalmazásba.

A fenti kérdésre a válasz végül a következő:
Hat alkalmazott van, akik közvetlenül vagy közvetve az Emp-nek jelentenek=3. Három alkalmazott, EmpId=7, EmpId=8 és EmpId=9 közvetlen beosztott, EmpId=10, EmpId=11 és EmpId=12 pedig közvetett beosztott.

A rekurzív CTE mechanikájának ismeretében könnyen megoldhatjuk a következő feladatokat.

• keresni az összes alkalmazottat, akik hierarchikusan az EmpId=10 felett állnak (kód itt)
• keresni az EmpId=8 közvetlen és a második szintű beosztottjait(kód itt)

A második példában a rekurziók számának korlátozásával szabályozzuk a hierarchia mélységét.
Az anchor elem adja a hierarchia első szintjét, ebben az esetben a közvetlen beosztottakat. Ezután minden rekurzió egy hierarchiaszinttel lejjebb lép az első szintről. A példában a kiindulópont az EmpId=8 és közvetlen beosztottjai. Az első rekurzió eggyel lejjebb mozog a hierarchiában, ahol az EmpId=8 ‘második szintű beosztottjai “élnek”.

Körkörös hivatkozási probléma

A hierarchiák egyik érdekessége, hogy a hierarchia tagjai zárt hurkot alkothatnak, ahol a hierarchia utolsó eleme hivatkozik az első elemre. A zárt hurkot körkörös hivatkozásnak is nevezik.
Az ilyen esetekben az implicit végpont, mint a korábban ismertetett INNER JOIN művelet, egyszerűen nem fog működni, mert mindig egy nem üres eredményhalmazt fog visszaadni a következő rekurzió folytatásához. A rekurziós rész addig fog gördülni, amíg el nem éri az Sql Server hibabiztos megoldását, a MAXRECURSION lekérdezési opciót.

A körkörös hivatkozási helyzet bemutatásához a korábban beállított tesztkörnyezet segítségével

• El kell távolítanunk a dbo.Employees táblából az elsődleges és idegen kulcsú korlátozásokat, hogy lehetővé tegyük a zárt ciklusok forgatókönyveit.
• Létrehozni egy körkörös hivatkozást (EmpId=10 fogja kezelni a közvetett vezetőjét , EmpId = 3)
• Kibővíteni az előző példákban használt tesztlekérdezést, hogy képes legyen elemezni a zárt hurokban lévő elemek hierarchiáját.

A kiterjesztett tesztlekérdezés itt található.

Mielőtt folytatnánk a körkörös hivatkozás példával, nézzük meg, hogyan működik a kiterjesztett tesztlekérdezés. Kommenteljük ki a WHERE záradék predikátumokat(az utolsó két sor), és futtassuk le a lekérdezést az eredeti dbo.Employee táblával szemben

8. ábra, Körkörös hurkok létezésének észlelése hierarchiában

A kiterjesztett lekérdezés eredménye pontosan ugyanaz, mint az előző kísérletben a 3. ábrán bemutatott eredmény. A kimenet a következő oszlopokkal bővül

• pth – Grafikusan ábrázolja az aktuális hierarchiát. Kezdetben a horgonyzó részen belül egyszerűen hozzáadja az első alárendeltet az MgrId=3-hoz, a menedzserhez, akitől indulunk. Most minden rekurzív elem az előző pth értéket veszi, és hozzáadja a következő alárendeltet.
• recLvl – a rekurzió aktuális szintjét reprezentálja. A lehorgonyzás végrehajtása recLvl=0
• isCircRef – körkörös hivatkozás meglétét érzékeli az aktuális hierarchiában(sorban). A rekurzív elem részeként olyan EmpId létezését keresi, amely korábban szerepelt a pth stringben.
  i.pl. ha az előző pth úgy néz ki, hogy 3->8->10 és az aktuális rekurzió hozzáadja ” ->3 “, (3->8 >10 -> 3) ami azt jelenti, hogy EmpId=3 nem csak közvetett felettese EmpId=10-nek, hanem EmpId=10 beosztottja is – én vagyok a főnök vagy a főnököd, és te vagy a főnököm fajta helyzet 😐

Végezzük most a szükséges módosításokat a dbo.Employees, hogy lássuk a kiterjesztett tesztlekérdezést működés közben.

Távolítsuk el a PK és FK korlátozásokat, hogy engedélyezzük a körkörös hivatkozásokat, és adjunk hozzá egy “rosszfiú körkörös ref”-et a táblához.

Futtassuk le a kiterjesztett tesztlekérdezést, és elemezzük az eredményeket (ne felejtsük el a korábban kommentált WHERE záradékot a szkript végén)
A szkript 100 rekurziót fog végrehajtani, mielőtt megszakad az alapértelmezett MAXRECURSION. A végeredmény két rekurzióra korlátozódik .. AND cte1.recLvl <= 2; ami az EmpId=3 hierarchia feloldásához szükséges.

A 9. ábra mutatja a zárt hurok hierarchiát, a megengedett maximális rekurziószám kimerítette a hibát és a kimenetet, amely a zárt hurkot mutatja.

10. ábra, Körkörös hivatkozás észlelése

Néhány megjegyzés a körkörös hivatkozás szkriptről.
A szkript csak egy ötlet arra, hogyan lehet zárt hurkokat találni hierarchiákban. Csak a körkörös hivatkozás első előfordulását jelenti – próbálja meg eltávolítani a WHERE záradékot, és figyelje meg az eredményt.
Véleményem szerint a szkript (vagy a szkript hasonló változatai) használhatóak termelési környezetben, pl. hibaelhárítási célokra vagy a meglévő hierarchiában a körkörös hivatkozások létrehozásának megelőzésére. Azonban megfelelő MAXRECURSION n-rel kell biztosítani, ahol n a hierarchia várható mélysége.

Ez a szkript nem relációs és egy traverzális technikára támaszkodik. Mindig az a legjobb megközelítés, ha deklaratív megkötéseket (PK, FK, CHECK…) használunk, hogy megakadályozzuk a zárt hurkokat az adatokban.

Futtatási terv elemzése

Ez a szegmens elmagyarázza, hogyan valósítja meg a Sql Server lekérdezésoptimalizálója(QO) a rekurzív CTE-t. Van egy közös minta, amelyet a QO a végrehajtási terv felépítésekor használ. Az eredeti tesztlekérdezés futtatása és a tényleges végrehajtási terv felvétele

A tesztlekérdezéshez hasonlóan a végrehajtási tervnek is két ága van: a horgonyzó ág és a rekurzív ág. Az UNION ALL operátort megvalósító konkatenációs operátor összekapcsolja a két részből származó eredményeket a lekérdezés eredményét alkotva.

Próbáljuk meg összeegyeztetni az előbb említett logikai végrehajtási sorrendet és a folyamat tényleges megvalósítását.

11. ábra, Rekurzív CTE végrehajtási terv

Az adatáramlást követve (jobbról balra irányban) a folyamat így néz ki:

Anchor elem (csak egyszer hajtódik végre)

1. Clustered Index Scan operátor – a rendszer végrehajtja az index beolvasást. Ebben a példában az MgrId = @EmpId kifejezést alkalmazza maradék predikátumként. A kiválasztott sorokat(EmpId és MgrId oszlopok) (soronként) visszaadja az előző operátornak.
2. Compute Scalar Az operátor hozzáad egy oszlopot a kimenethez. Ebben a példában a hozzáadott oszlop neve . Ez a rekurziók számát jelenti. Az oszlop kezdeti értéke 0; =0
3. Konkatenáció – egyesíti a két ág bemeneteit. Az első iterációban az operátor csak a horgonyzó ágból kap sorokat. A kimeneti oszlopok nevét is megváltoztatja. Ebben a példában az új oszlopnevek a következők:
   1. = vagy * * a rekurzív ágban hozzárendelt rekurziók számát tartja. Az első iterációban nincs értéke.
   2. = EmpId(a horgonyzó részből) vagy EmpId(a rekurzív részből)
   3. = MgrId(a horgonyzó részből) vagy MgrId (a rekurzív részből)
4. Index Spool (Lazy Spool) Ez az operátor a Concatenation operátorból kapott eredményt egy munkatáblában tárolja. A “Logikai művelet” tulajdonsága “Lazy Spool”. Ez azt jelenti, hogy az operátor azonnal visszaadja a bemeneti sorokat, és nem halmozza fel az összes sort, amíg meg nem kapja a végső eredménykészletet (Eager Spool) . A munkatábla egy klaszterezett indexként van felépítve, amelynek kulcsoszlopa a rekurzió száma. Mivel az indexkulcs nem egyedi, a rendszer egy belső, 4 bájtos egyedi azonosítót ad az indexkulcshoz annak biztosítása érdekében, hogy az indexben lévő összes sor a fizikai végrehajtás szempontjából egyedileg azonosítható legyen. Az operátor “With Stack” tulajdonsága is “True”-ra van állítva, ami az orsóoperátornak ezt a változatát egy Stack Spool operátorrá teszi A Stack Spool operátornak mindig két összetevője van – egy Index Spool, amely felépíti az indexstruktúrát, és egy Table Spool, amely az Index Spool által felépített munkatáblában tárolt sorok fogyasztójaként működik.
   Az Index Spool operátor ebben a szakaszban sorokat ad vissza a SELECT operátornak, és ugyanezeket a sorokat tárolja a munkatáblában.
5. A SELECT operátor visszaadja az EmpId és MgrId ( , ) sorokat. Kizárja az eredményből. A sorokat a hálózati pufferbe küldi, ahogyan azok a következő operátoroktól érkeznek

Az Index Scan operátor összes sorának kimerítése után a Concatenation operátor kontextust vált a rekurzív ágra. A lehorgonyzó ág a folyamat során nem hajtódik végre újra.

Rekurzív elem

1. Táblázati orsó (Lazy Spool). Az operátornak nincsenek bemenetei, és a (4) pontban említettek szerint az Index Spool által előállított és egy klaszterezett munkatáblában tárolt sorok fogyasztójaként működik. Az “Elsődleges csomópont” tulajdonsága 0-ra van állítva, ami az Index Spool Node Id-re mutat. Kiemeli a két operátor függőségét. Az operátor
   1. eltávolítja az előző rekurzióban beolvasott sorokat. Ez az első rekurzió, és nincsenek korábban beolvasott sorok, amelyeket törölni kellene. A munkatábla három sort tartalmaz (4. ábra).
   2. A beolvasott sorok az indexkulcs + egyedi azonosító szerint rendezve, csökkenő sorrendben. Ebben a példában az első beolvasott sor EmpId=9, MgrId=3.

   Végül az operátor átnevezi a kimeneti oszlopneveket. =, = és lesz .
   MEGJEGYZÉS: A táblázatkitöltő operátor az INNER JOIN jobb oldalán lévő cte1 kifejezésként figyelhető meg (4. ábra)

2. Compute Scalar Az operátor a korábban az .oszlopban tárolt aktuális rekurziószámhoz hozzáad 1-t. Az eredményt egy új oszlopban tárolja, . = + 1 = 0 + 1 = 1. Az operátor három oszlopot ad ki, a kettőt a Table Spoolból ( és ) és
3. Nested Loop(I) operátor sorokat kap a külső bemenetéről, ami az előző lépésből származó Compute Scalar, majd a – a Table Spool operátorból származó EmpId-t képviseli, mint maradék predikátumot a Loop belső bemenetén elhelyezett Index Scan operátorban. A belső bemenet a külső bemenet minden egyes sorára egyszer végrehajtódik.
4. Az Index Scan operátor a dbo.Employees táblából (két oszlop; EmpId és MgrId) az összes minősített sort visszaadja a beágyazott ciklus operátornak.
5. Beágyazott ciklus(II): Az operátor a külső bemenetből és az EmpId és MgrId a belső bemenetből kombinálja, és a három oszlop sorait átadja a következő operátornak.
6. Az Assert operátor olyan feltételek ellenőrzésére szolgál, amelyek a lekérdezés hibaüzenettel történő megszakítását igénylik. Rekurzív CTE-k esetében az assert operátor a “MAXRECURSION n” lekérdezési opciót valósítja meg. Ellenőrzi, hogy a rekurzív rész elérte-e a megengedett (n) rekurziószámot vagy sem. Ha az aktuális rekurziók száma (lásd a 7. lépést) nagyobb, mint (n), az operátor 0-t ad vissza, ami futási hibát okoz. Ebben a példában az Sql Server az alapértelmezett MAXRECURSION 100-as értéket használja. A kifejezés így néz ki: CASE WHEN > 100 THEN 0 ELSE NULL Ha úgy döntünk, hogy a MAXRECURSION 0 hozzáadásával kizárjuk a hibabiztosságot, az assert operátor nem fog szerepelni a tervben.
7. Az összekapcsolás a két ág bemeneteit egyesíti. Ezúttal csak a rekurzív részből kap bemenetet, és a 3. lépésnek megfelelően oszlopokat/sorokat ad ki.
8. Index Spool (Lazy Spool) a konkatenációs operátor kimenetét hozzáadja a munkatáblához, majd átadja a SELECT operátornak. Ekkor a munkatábla összesen 4 sort tartalmaz: három sort a horgonyzó végrehajtásból és egyet az első rekurzióból. A munkatábla klaszterezett indexszerkezetét követve az új sor a munkatábla végére kerül

9. A folyamat most a 6. lépéstől folytatódik. A table spool operátor eltávolítja a korábban beolvasott sorokat (az első három sort) a munkatáblából, és beolvassa az utoljára beillesztett sort, a negyedik sort.

Következtetés

CTE(Common table expressions) az Sql Serverben elérhető táblakifejezések egyik típusa. A CTE egy független táblázatkifejezés, amelyet meg lehet nevezni és egyszer vagy többször lehet rá hivatkozni a fő lekérdezésben.
A CTE-k egyik legfontosabb felhasználási területe a rekurzív lekérdezések írása. A rekurzív CTE-k mindig ugyanazt a struktúrát követik – horgony lekérdezés, UNION ALL többszörös halmazoperátor, rekurzív tag és a rekurziót előidéző utasítás. A rekurzív CTE deklaratív rekurzió, és mint ilyen, más tulajdonságokkal rendelkezik, mint imperatív megfelelője, pl. a deklaratív rekurzió befejezésének ellenőrzése implicit jellegű – a rekurziós folyamat akkor áll le, ha az előző cte-ben nincs visszaadott sor.