Hierarchikus adatok (SQL Server)

2025-05-19

A következőkre vonatkozik:SQL Server Azure SQL Database Felügyelt Azure SQL-példány SQL-adatbázis a Microsoft Fabric

A beépített hierarchiaazonosító adattípus megkönnyíti a hierarchikus adatok tárolását és lekérdezését. hierarchiaazonosító a leggyakoribb hierarchikus adattípusnak számító fák ábrázolása érdekében van optimalizálva.

A hierarchikus adatok olyan adatelemek készleteként vannak definiálva, amelyek hierarchikus kapcsolatok alapján kapcsolódnak egymáshoz. Hierarchikus kapcsolatok léteznek, ahol az egyik adatelem egy másik elem szülője. Az adatbázisokban gyakran tárolt hierarchikus adatok például a következő elemeket tartalmazzák:

Szervezeti struktúra
Fájlrendszer
Tevékenységek egy projektben
A nyelvi kifejezések osztályozása
Weblapok közötti hivatkozások grafikonja

A hierarchiaazonosító adattípussal hierarchikus szerkezetű táblákat hozhat létre, vagy leírhatja a más helyen tárolt adatok hierarchikus struktúráját. A hierarchikus adatok lekérdezéséhez és kezeléséhez használja a Transact-SQL hierarchiaazonosító függvényeket.

Kulcstulajdonságok

A hierarchiaazonosító adattípus értéke egy fahierarchiában lévő pozíciót jelöl. A hierarchiaazonosító értékei a következő tulajdonságokkal rendelkeznek:

Rendkívül kompakt

Azoknak a biteknek az átlagos száma, amelyek szükségesek ahhoz, hogy egy csomópontot ábrázoljanak egy n csomópontot tartalmazó fában, az átlagos ágterjedéstől függ (egy csomópont gyermekeinek átlagos száma). A kis méretű (0-7) kifúvatások esetében a méret körülbelül $6log{A}{n}$ bit, ahol az A az átlagos kifúvatás. Egy 100 000 fős szervezeti hierarchia csomópontja, amelynek átlagosan hat szintje van, körülbelül 38 bitet vesz igénybe. Ez 40 bitre vagy 5 bájtra kerekítve van a tároláshoz.
Az összehasonlítás mélységi bejárás sorrendje szerint történik.

Ha adott két hierarchiaazonosító érték, a és b, akkor a < b azt jelenti, hogy a megelőzi b egy fa mélységi bejárásában. A hierarchiaazonosító adattípusainak indexei mélységi sorrendben vannak, és azok a csomópontok, amelyek mélységi bejárás során közel vannak egymáshoz, egymás közelében kerülnek tárolásra. Egy rekord gyermekei például a rekord mellett vannak tárolva.
Tetszőleges beszúrások és törlések támogatása

A GetDescendant (adatbázismotor) metódussal mindig létrehozhat egy testvért az adott csomóponttól jobbra, egy adott csomóponttól balra vagy két testvér között. Az összehasonlító tulajdonság akkor marad fenn, ha tetszőleges számú csomópontot szúr be vagy töröl a hierarchiából. A legtöbb beszúrás és törlés megőrzi a tömörítési tulajdonságot. A két csomópont közötti beszúrások azonban hierarchiaazonosítót eredményeznek, értékeket valamivel kevésbé kompakt megjelenítéssel.

Korlátozások

A hierarchiaazonosító adattípusra a következő korlátozások vonatkoznak:

A hierarchiaazonosító típusú oszlopok nem jelölik automatikusan a fát. Az alkalmazás feladata hierarchiaazonosító értékek létrehozása és hozzárendelése oly módon, hogy a sorok közötti kívánt kapcsolat tükröződjön az értékekben. Egyes alkalmazásokhoz hierarchiaazonosítós oszlop tartozik, amely egy másik táblában definiált hierarchia helyét jelzi.
Az alkalmazáson múlik az egyidejűség kezelése hierarchiaazonosító értékek létrehozása és hozzárendelése során. Nincs garancia arra, hogy egy oszlopban hierarchiaazonosító értékek egyediek, kivéve, ha az alkalmazás egyedi kulcskényszert használ, vagy saját logikájával kényszeríti ki magát az egyediséget.
Az hierarchiaazonosítóval értékek által képviselt hierarchikus kapcsolatok nincsenek kényszerítve idegen kulcsos kapcsolatokként. Lehetséges, és néha helyénvaló, hogy egy hierarchikus kapcsolatban a A-hoz egy Bgyermek tartozik, majd a A törlődik, így a B egy nem létező rekordhoz kapcsolódik. Ha ez a viselkedés elfogadhatatlan, az alkalmazásnak le kell kérdeznie a leszármazottakat a szülők törlése előtt.

Mikor érdemes a hierarchiaazonosító alternatíváit használni?

Két alternatíva van a hierarchikus adatok ábrázolására szolgáló hierarchiaazonosító helyett:

Szülő/gyermek
XML

hierarchiaazonosító általában felülmúlja ezeket az alternatívákat. Vannak azonban olyan konkrét helyzetek, amelyeket ebben a cikkben részletezünk, ahol az alternatívák valószínűleg jobbak.

Szülő/gyermek

Ha a szülő/gyermek megközelítést használja, minden sor tartalmaz egy hivatkozást a szülőre. Az alábbi táblázat egy tipikus táblát határoz meg, amely egy szülő-gyermek kapcsolat szülő- és gyermeksorait tartalmazza:

USE AdventureWorks2022;
GO

CREATE TABLE ParentChildOrg (
    BusinessEntityID INT PRIMARY KEY,
    ManagerId INT FOREIGN KEY REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName NVARCHAR(50)
);
GO

A szülő/gyermek és hierarchiaazonosító összehasonlítása a gyakori műveletek során:

Az altábla-lekérdezések jelentősen gyorsabbak a hierarchiaazonosítóval.
A közvetlen leszármazott lekérdezések valamivel lassabbak hierarchiaazonosítóval.
A nem levél csomópontok áthelyezése lassabb a hierarchiaazonosítóval.
A nem levél csomópontok beszúrása és a levélcsomópontok beszúrása vagy áthelyezése ugyanolyan összetettséggel bír hierarchia-azonosítóval.

A szülő/gyermek magasabb szintű lehet, ha a következő feltételek teljesülnek:

A kulcs mérete kritikus fontosságú. Azonos számú csomópont esetén a hierarchiaazonosító értéke egyenlő vagy nagyobb, mint egy, az egész számok családjába tartozó érték (kisként, int, bigint). Csak ritka esetekben célszerű szülő/gyermek struktúrát használni, mert a hierarchiaazonosító jelentősen jobb I/O- és CPU-lokalitást és összetettséget kínál, mint a szülő-gyermek struktúra alkalmazásakor szükséges közös táblakifejezések.
A lekérdezések ritkán kérdeznek le a hierarchia egyes szakaszaiban. Más szóval a lekérdezések általában csak egy pontot kezelnek a hierarchiában. Ezekben az esetekben a közös elhelyezés nem fontos. Például a szülő-gyermek viszony előnyt jelent, ha a szervezeti táblát kizárólag az egyes alkalmazottak bérszámfejtésének feldolgozására szolgál.
A nem levél részfák gyakran mozognak, és a teljesítmény nagyon fontos. A szülő-gyermek ábrázolásban a sorok hierarchiában való helyének módosítása egyetlen sort érint. Egy sor helyének módosítása egy hierarchiaazonosítóban használat hatással van n sorra, ahol n az áthelyezendő részhalmaz csomópontjainak száma.

Ha a nemlevél alstruktúrák gyakran változnak, és a teljesítmény fontos, de a legtöbb változás a hierarchia egy jól meghatározott szintjén történik, fontolja meg a magasabb és alacsonyabb szinteket két külön hierarchiára bontani. Minden áthelyeződik a magasabb hierarchia levélszintjeire. Vegyük például egy szolgáltatás által üzemeltetett webhelyek hierarchiáját. A webhelyek számos, hierarchikus módon elrendezett lapot tartalmaznak. Előfordulhat, hogy az üzemeltetett webhelyek a helyhierarchiában más helyekre kerülnek át, de az alárendelt lapok ritkán vannak átrendezve. Ez a következő módon jelenhet meg:
```
CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO
```

XML

Az XML-dokumentum egy fa, ezért egyetlen XML-adattípus-példány teljes hierarchiát jelölhet. Az SQL Serverben xml-index létrehozásakor hierarchiaazonosító értékek belsőleg a hierarchia pozíciójának megjelenítésére szolgálnak.

Az XML-adattípus használata akkor lehet jobb, ha az alábbiak mindegyike igaz:

A teljes hierarchiát a rendszer mindig tárolja és kéri le.
Az adatokat az alkalmazás XML formátumban használja fel.
A predikátumkeresések rendkívül korlátozottak, és nem teljesítménykritikusak.

Ha például egy alkalmazás több szervezetet követ nyomon, mindig tárolja és lekéri a teljes szervezeti hierarchiát, és nem kérdez le egyetlen szervezetet sem, az alábbi űrlap táblájának van értelme:

CREATE TABLE XMLOrg (
    Orgid INT,
    Orgdata XML
);
GO

Hierarchikus adatok indexelési stratégiái

A hierarchikus adatok indexelésére két stratégia létezik:

Mélységi keresés

A mélységi első index egy részhalmazban tárolja a sorokat egymás közelében. Például az összes olyan alkalmazott, aki jelentést tesz a felettesének, a felettesük adatai mellett tárolódik.

Az első mélységi indexben a csomópont altartományában lévő összes csomópont együtt van helyezve. Az első mélységi indexek tehát hatékonyak az almappákkal kapcsolatos lekérdezések megválaszolásához, például: "Az összes fájl keresése ebben a mappában és almappáiban"
Szélességi bejárás

A teljes körű első index a hierarchia egyes szintjeinek sorait együtt tárolja. Az ugyanazon vezetőnek közvetlenül jelentést készítő alkalmazottak rekordjait például egymás közelében tárolja a rendszer.

Szélességi indexelésben egy csomópont összes közvetlen gyermeke együtt van elhelyezve. Szélességi sorrendű indexek tehát hatékonyak az azonnali alárendeltekkel kapcsolatos lekérdezések megválaszolására, például: "Keresse meg az összes olyan alkalmazottat, aki közvetlenül ezen vezető alá tartozik."

Az, hogy mélységi első, szélességi első, mindkettő vagy egyik sem alkalmazandó, és hogy melyik legyen a fürtkulcs (ha van ilyen), a fenti lekérdezéstípusok relatív fontosságától és a SELECT kontra DML műveletek viszonylagos fontosságától függ. Részletes példa az indexelési stratégiákra: Oktatóanyag: A hierarchiaazonosító adattípus használata.

Indexek létrehozása

A GetLevel() metódussal létrehozhat egy szélességű első rendelést. Az alábbi példában a szélesség-első és a mélységi első index is létrejön:

USE AdventureWorks2022;
GO

CREATE TABLE Organization (
    BusinessEntityID HIERARCHYID,
    OrgLevel AS BusinessEntityID.GetLevel(),
    EmployeeName NVARCHAR(50) NOT NULL
);
GO

CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization (OrgLevel, BusinessEntityID);
GO

CREATE UNIQUE INDEX Org_Depth_First
ON Organization (BusinessEntityID);
GO

Példák

A cikkben szereplő kódminták a AdventureWorks2022 vagy AdventureWorksDW2022 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

Egyszerű példa

Az alábbi példa szándékosan leegyszerűsíti az első lépéseket. Először hozzon létre egy táblát a földrajzi adatok tárolásához.

CREATE TABLE BasicDemo (
    [Level] HIERARCHYID NOT NULL,
    Location NVARCHAR(30) NOT NULL,
    LocationType NVARCHAR(9) NULL
);

Most szúrjon be adatokat egyes kontinensekre, országokra/régiókra, államokra és városokra.

INSERT BasicDemo
VALUES ('/1/', 'Europe', 'Continent'),
    ('/2/', 'South America', 'Continent'),
    ('/1/1/', 'France', 'Country'),
    ('/1/1/1/', 'Paris', 'City'),
    ('/1/2/1/', 'Madrid', 'City'),
    ('/1/2/', 'Spain', 'Country'),
    ('/3/', 'Antarctica', 'Continent'),
    ('/2/1/', 'Brazil', 'Country'),
    ('/2/1/1/', 'Brasilia', 'City'),
    ('/2/1/2/', 'Bahia', 'State'),
    ('/2/1/2/1/', 'Salvador', 'City'),
    ('/3/1/', 'McMurdo Station', 'City');

Jelölje ki az adatokat, és adjon hozzá egy oszlopot, amely a Level adatokat könnyen érthető szöveges értékké alakítja. Ez a lekérdezés az eredményt a hierarchiaazonosító adattípus alapján is sorrendbe állítja.

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

Itt vannak az eredmények.

Converted Level  Level     Location         LocationType
---------------  --------  ---------------  ---------------
/1/              0x58      Europe           Continent
/1/1/            0x5AC0    France           Country
/1/1/1/          0x5AD6    Paris            City
/1/2/            0x5B40    Spain            Country
/1/2/1/          0x5B56    Madrid           City
/2/              0x68      South America    Continent
/2/1/            0x6AC0    Brazil           Country
/2/1/1/          0x6AD6    Brasilia         City
/2/1/2/          0x6ADA    Bahia            State
/2/1/2/1/        0x6ADAB0  Salvador         City
/3/              0x78      Antarctica       Continent
/3/1/            0x7AC0    McMurdo Station  City

A hierarchia érvényes struktúrával rendelkezik, annak ellenére, hogy belsőleg nem konzisztens. Bahia az egyetlen állam. A hierarchiában Brasilia városának társtársaként jelenik meg. Hasonlóképpen, a McMurdo állomásnak nincs szülőországa/régiója. A felhasználóknak el kell dönteniük, hogy az ilyen típusú hierarchia megfelelő-e a használatukhoz.

Adjon hozzá egy másik sort, és válassza ki az eredményeket.

INSERT BasicDemo
VALUES ('/1/3/1/', 'Kyoto', 'City'),
    ('/1/3/1/', 'London', 'City');

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

Ez további lehetséges problémákat mutat be. Kiotó akkor is beszúrható a /1/3/1/ szintként, ha nincs /1/3/szülőszint. Londonnak és Kiotónak is ugyanaz az értéke a hierarchiaazonosító. A felhasználóknak ismét el kell dönteniük, hogy az ilyen típusú hierarchia megfelelő-e a használatukhoz, és blokkolniuk kell a használatukhoz érvénytelen értékeket.

Emellett ez a táblázat nem használta a hierarchia '/'tetejét. Azért nem, mert nincs közös szülője az összes kontinensnek. Hozzáadhat egyet azáltal, hogy az egész bolygót hozzáadja.

INSERT BasicDemo
VALUES ('/', 'Earth', 'Planet');

Migrálás szülőről/gyermekről hierarchiaazonosítóra

A legtöbb fa szülő-gyermek hierarchia alapján van ábrázolva. A legegyszerűbb módja annak, hogy egy szülő-gyermek struktúráról hierarchiaazonosító használatával táblára váltsunk, ha ideiglenes oszlopot vagy ideiglenes táblát használunk a hierarchia minden szintjén lévő csomópontok számának nyomon követésére. A szülő/gyermek tábla áttelepítésére példaként tekintse meg oktatóanyag 1. leckéjét: A hierarchiaazonosító adattípus használata.

Fa kezelése hierarchiaazonosítóval

Bár egy hierarchiaazonosító oszlop nem feltétlenül jelöl fát, az alkalmazás könnyen meggyőződhet arról, hogy igen.

Új értékek létrehozásakor hajtsa végre az alábbi lépések egyikét:
- A szülősor utolsó gyermekszámának nyomon követése.
- Az utolsó gyermek kiszámítása. Ennek hatékony elvégzéséhez egy első szintű indexre van szükség.
Az egyediség biztosítása érdekében hozzunk létre egy egyedi indexet az oszlopon, esetleg egy klaszterezési kulcs részeként. Az egyedi értékek beszúrásának biztosításához hajtsa végre az alábbi lépések egyikét:
- Egyedi kulcssértési hibák észlelése és újrapróbálkozás.
- Határozza meg az egyes új gyermekcsomópontok egyediségét, és szúrja be egy szerializálható tranzakció részeként.

Példa hibaészlelésre

A következő példában a mintakód kiszámítja az új gyermek EmployeeId értékét, majd észleli a kulcssértéseket, és visszatér INS_EMP jelölőhöz az új sor EmployeeId értékének újrafordításához:

USE AdventureWorks;
GO

CREATE TABLE Org_T1 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    OrgLevel AS EmployeeId.GetLevel(),
    EmployeeName NVARCHAR(50)
);
GO

CREATE INDEX Org_BreadthFirst ON Org_T1 (
    OrgLevel,
    EmployeeId
);
GO

CREATE PROCEDURE AddEmp (
    @mgrid HIERARCHYID,
    @EmpName NVARCHAR(50)
)
AS
BEGIN
    DECLARE @last_child HIERARCHYID;

    INS_EMP:

    SELECT @last_child = MAX(EmployeeId)
    FROM Org_T1
    WHERE EmployeeId.GetAncestor(1) = @mgrid;

    INSERT INTO Org_T1 (EmployeeId, EmployeeName)
    SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName;

    -- On error, return to INS_EMP to recompute @last_child
    IF @@error <> 0
        GOTO INS_EMP
END;
GO

Példa szerializálható tranzakció használatával

A Org_BreadthFirst index biztosítja, hogy a @last_child meghatározása tartománykeresést használjon. Az egyéb hibaeseteken kívül, ha egy alkalmazás ellenőrizni szeretné, a beszúrás után ismétlődő kulcssértés azt jelzi, hogy több alkalmazottat is fel szeretne venni ugyanazzal az azonosítóval, ezért @last_child újra kell írni. A következő kód kiszámítja az új csomópont értékét egy szerializálható tranzakción belül:

CREATE TABLE Org_T2 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    LastChild HIERARCHYID,
    EmployeeName NVARCHAR(50)
);
GO

CREATE PROCEDURE AddEmp (
    @mgrid HIERARCHYID,
    @EmpName NVARCHAR(50)
)
AS
BEGIN
    DECLARE @last_child HIERARCHYID;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @last_child = EmployeeId.GetDescendant(LastChild, NULL)
    FROM Org_T2
    WHERE EmployeeId = @mgrid;

    UPDATE Org_T2
    SET LastChild = @last_child
    WHERE EmployeeId = @mgrid;

    INSERT Org_T2 (EmployeeId, EmployeeName)
    VALUES (@last_child, @EmpName);

    COMMIT;
END;

Az alábbi kód három sorba tölti fel a táblát, és visszaadja az eredményeket:

INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES (HIERARCHYID::GetRoot(), 'David');
GO

EXECUTE AddEmp 0x, 'Sariya';
GO

EXECUTE AddEmp 0x58, 'Mary';
GO

SELECT * FROM Org_T2

Itt vannak az eredmények.

EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

Fa kényszerítése

Az előző példák azt szemléltetik, hogy egy alkalmazás hogyan biztosíthatja a fa karbantartását. Ha egy fát kényszerekkel szeretne érvényesíteni, létrehozhat egy számított oszlopot, amely az egyes csomópontok szülőjét határozza meg, és idegen kulcs kényszerrel kapcsolódik vissza az elsődleges kulcsazonosítóhoz.

CREATE TABLE Org_T3 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    ParentId AS EmployeeId.GetAncestor(1) PERSISTED FOREIGN KEY REFERENCES Org_T3(EmployeeId),
    LastChild HIERARCHYID,
    EmployeeName NVARCHAR(50)
);
GO

Ez a kapcsolat kikényszerítési módszere akkor ajánlott, ha a hierarchikus fa fenntartásához nem megbízható kód közvetlen DML-hozzáféréssel rendelkezik a táblához. Ez a módszer azonban csökkentheti a teljesítményt, mert a korlátozást minden DML-műveleten ellenőrizni kell.

Elődök keresése a CLR használatával

A hierarchia két csomópontjának közös művelete a legalacsonyabb közös ős megkeresése. Ez a feladat Transact-SQL vagy CLR formátumban írható, mert a hierarchiaazonosító típusa mindkettőben elérhető. A CLR azért ajánlott, mert a teljesítmény gyorsabb.

A következő CLR-kóddal listázhatja az elődöket, és megkeresheti a legkisebb közös őst:

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server; // SqlFunction Attribute
using Microsoft.SqlServer.Types;  // SqlHierarchyId

public partial class HierarchyId_Operations
{
    [SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
    public static IEnumerable ListAncestors(SqlHierarchyId h)
    {
        while (!h.IsNull)
        {
            yield return (h);
            h = h.GetAncestor(1);
        }
    }

    public static void FillRow_ListAncestors(
        Object obj,
        out SqlHierarchyId ancestor
    )
    {
        ancestor = (SqlHierarchyId)obj;
    }

    public static HierarchyId CommonAncestor(
        SqlHierarchyId h1,
        HierarchyId h2
    )
    {
        while (!h1.IsDescendantOf(h2))
        {
            h1 = h1.GetAncestor(1);
        }

        return h1;
    }
}

Az alábbi Transact-SQL példákban szereplő ListAncestor és CommonAncestor metódusok használatához hozza létre a DLL-t, és hozza létre a HierarchyId_Operations szerelvényt az SQL Serveren az alábbi példához hasonló kód végrehajtásával:

CREATE ASSEMBLY HierarchyId_Operations
    FROM '<path to DLL>\ListAncestors.dll';
GO

Elődök listázása

A csomópontok elődeinek listájának létrehozása gyakori művelet, például a szervezet pozícióinak megjelenítése. Ennek egyik módja egy táblaértékű függvény használata a korábban definiált HierarchyId_Operations osztály használatával:

A Transact-SQL használata:

CREATE FUNCTION ListAncestors (@node HIERARCHYID)
RETURNS TABLE (node HIERARCHYID)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors;
GO

Példa a használatra:

DECLARE @h AS HIERARCHYID;

SELECT @h = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID,
    OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
INNER JOIN ListAncestors(@h) AS A
    ON ED.OrgNode = A.Node
GO

A legkisebb közös előd megkeresése

A korábban definiált HierarchyId_Operations osztály használatával hozza létre a következő Transact-SQL függvényt, hogy megtalálja a hierarchiában két csomópontot tartalmazó legkisebb közös elődet:

CREATE FUNCTION CommonAncestor (
    @node1 HIERARCHYID,
    @node2 HIERARCHYID
)
RETURNS HIERARCHYID
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor;
GO