Hiërarchische gegevens (SQL Server)

2025-05-20

Van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-database in Microsoft Fabric

Het ingebouwde gegevenstype hierarchyid maakt het gemakkelijker om hiërarchische gegevens op te slaan en er query's op uit te voeren. hierarchyid is geoptimaliseerd voor het weergeven van bomen, wat het meest voorkomende type hiërarchische gegevens is.

Hiërarchische gegevens worden gedefinieerd als een set gegevensitems die aan elkaar zijn gerelateerd door hiërarchische relaties. Hiërarchische relaties bestaan waarbij één gegevensitem het bovenliggende item van een ander item is. Voorbeelden van hiërarchische gegevens die vaak worden opgeslagen in databases zijn de volgende items:

Een organisatiestructuur
Een bestandssysteem
Een set taken in een project
Een taxonomie van taaltermen
Een grafiek met koppelingen tussen webpagina's

Gebruik hiërarchie-id als gegevenstype om tabellen te maken met een hiërarchische structuur of om de hiërarchische structuur van gegevens te beschrijven die op een andere locatie zijn opgeslagen. Gebruik de hiërarchie-id-functies in Transact-SQL om hiërarchische gegevens op te vragen en te beheren.

Sleuteleigenschappen

Een waarde van de hiërarchie-id gegevenstype vertegenwoordigt een positie in een structuurhiërarchie. Waarden voor hierarchyid de volgende eigenschappen hebben:

Extreem compact

Het gemiddelde aantal bits dat nodig is om een knooppunt in een boomstructuur weer te geven met n knooppunten, is afhankelijk van de gemiddelde fanout (het gemiddelde aantal onderliggende elementen van een knooppunt). Voor kleine fanouts (0-7) is de grootte ongeveer $6log{A}{n}$ bits, waarbij A de gemiddelde fanout is. Een knooppunt in een organisatiehiërarchie van 100.000 mensen met een gemiddelde fanout van zes niveaus vergt ongeveer 38 bits. Dit wordt afgerond op 40 bits of 5 bytes voor opslag.
Vergelijking is in diepte-eerste volgorde

Gezien twee hiërarchie-id-waardena en b, a < b betekent dat a voordat b in een diepte-eerste doorzoeking van de boom voorkomt. Indexen op hiërarchie-id gegevenstypen zijn in uitgebreide volgorde en knooppunten dicht bij elkaar in een diepte-eerste doorkruising worden bij elkaar opgeslagen. De kinderen van een record worden bijvoorbeeld naast dat record opgeslagen.
Ondersteuning voor willekeurige invoegingen en verwijderingen

Met behulp van de Methode GetDescendant (Database Engine) is het altijd mogelijk om een knooppunt rechts van een bepaald knooppunt, links van een bepaald knooppunt of tussen twee broers en zussen te genereren. De vergelijkingseigenschap wordt gehandhaafd wanneer een willekeurig aantal knooppunten wordt ingevoegd of verwijderd uit de hiërarchie. De meeste invoegingen en verwijderingen behouden de compactheidseigenschap. Invoegingen tussen twee knooppunten produceren echter hiërarchie-id-waarden met een iets minder compacte weergave.

Beperkingen

Het gegevenstype hierarchyid heeft de volgende beperkingen:

Een kolom van het type hierarchyid vertegenwoordigt niet automatisch een boom. Het is aan de toepassing om hiërarchie-id-waarden op een zodanige manier te genereren en toe te wijzen dat de gewenste relatie tussen rijen wordt weergegeven in de waarden. Sommige toepassingen hebben mogelijk een kolom van het type hierarchyid die de locatie aangeeft in een hiërarchie die is gedefinieerd in een andere tabel.
Het is aan de toepassing om gelijktijdigheid te beheren bij het genereren en toewijzen van hiërarchie-id-waarden . Er is geen garantie dat hiërarchie-id-waarden in een kolom uniek zijn, tenzij de toepassing een unieke sleutelbeperking gebruikt of uniekheid zelf afdwingt via een eigen logica.
Hiërarchische relaties, vertegenwoordigd door hiërarchie-id-waarden, worden niet afgedwongen zoals een foreign key-relatie. Het is mogelijk en soms passend om een hiërarchische relatie te hebben waarbij A een onderliggend element B is en vervolgens A wordt verwijderd, waarbij B een relatie met een niet-bestaand record overblijft. Als dit gedrag onaanvaardbaar is, moet de toepassing een query uitvoeren op afstammelingen voordat ouders worden verwijdert.

Wanneer alternatieven voor hiërarchie-id gebruiken

Twee alternatieven voor hierarchyid voor het weergeven van hiërarchische gegevens zijn:

Ouder/kind
XML

hierarchyid is over het algemeen beter dan deze alternatieven. Er zijn echter specifieke situaties, die in dit artikel worden beschreven, waarbij de alternatieven waarschijnlijk beter zijn.

Ouder/kind

Wanneer u de ouder/kind-benadering gebruikt, bevat elke rij een verwijzing naar de ouder. De volgende tabel definieert een typische tabel die wordt gebruikt om de ouder- en kindrijen op te slaan in een ouder-kindrelatie.

USE AdventureWorks2022;
GO

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

Vergelijking van ouder/kind en hierarchyid voor algemene bewerkingen:

Queries met substructuur zijn aanzienlijk sneller met hierarchyid.
Directe afstammelingenquery's zijn iets langzamer met hierarchyid.
Het verplaatsen van niet-bladknopen is langzamer met hierarchyid.
Het invoegen van niet-blad knooppunten en het invoegen of verplaatsen van bladknooppunten heeft dezelfde complexiteit als hiërarchie-id.

Bovenliggend/onderliggend element kan superieur zijn wanneer de volgende voorwaarden bestaan:

De grootte van de sleutel is kritiek. Voor hetzelfde aantal knooppunten is een hiërarchie-id waarde gelijk aan of groter dan een waarde van een getaltype uit de integer-familie (smallint, int, bigint). Dit is slechts een reden om in zeldzame gevallen bovenliggende/onderliggende structuren te gebruiken, omdat hierarchyid aanzienlijk efficiënter is qua I/O- en CPU-verwerking dan de algemene tabelexpressies die nodig zijn wanneer een bovenliggende/onderliggende structuur wordt gebruikt.
Queries voeren zelden zoekopdrachten uit op delen van de hiërarchie. Met andere woorden, query's adresseren meestal slechts één punt in de hiërarchie. In deze gevallen is colocatie niet belangrijk. Ouder/kind is bijvoorbeeld beter wanneer de organisatietabel alleen wordt gebruikt voor het verwerken van de payroll voor individuele werknemers.
Niet-bladsubstructuren worden vaak verplaatst en de prestaties zijn zeer belangrijk. In een bovenliggende/onderliggende weergave heeft het wijzigen van de locatie van een rij in een hiërarchie invloed op slechts één rij. Het wijzigen van de locatie van een rij in een hiërarchie-id is van invloed op n rijen, waarbij n het aantal knooppunten in de substructuur is dat wordt verplaatst.

Als de niet-blad substructuren vaak worden verplaatst en de prestaties belangrijk zijn, maar de meeste verplaatsingen zich op een goed gedefinieerd niveau binnen de hiërarchie bevinden, overweeg dan om de hogere en lagere niveaus in twee hiërarchieën te splitsen. Hierdoor worden alle zetten naar bladniveaus in de hogere hiërarchie gemaakt. Denk bijvoorbeeld aan een hiërarchie van websites die worden gehost door een service. Sites bevatten veel pagina's die op een hiërarchische manier zijn gerangschikt. Gehoste sites kunnen worden verplaatst naar andere locaties in de sitehiërarchie, maar de onderliggende pagina's worden zelden opnieuw gerangschikt. Dit kan worden weergegeven via:
```
CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO
```

XML

Een XML-document is een structuur en daarom kan één exemplaar van het XML-gegevenstype een volledige hiërarchie vertegenwoordigen. In SQL Server wanneer een XML-index wordt gemaakt, worden hiërarchie-id-waarden intern gebruikt om de positie in de hiërarchie weer te geven.

Het gebruik van een XML-gegevenstype kan beter zijn als aan alle volgende voorwaarden wordt voldaan:

De volledige hiërarchie wordt altijd opgeslagen en opgehaald.
De gegevens worden door de toepassing gebruikt in XML-indeling.
Predicaatzoekopdrachten zijn zeer beperkt en zijn niet essentieel voor prestaties.

Als een toepassing bijvoorbeeld meerdere organisaties bijhoudt, altijd de volledige organisatiehiërarchie opslaat en ophaalt en geen query's uitvoert in één organisatie, kan een tabel van het volgende formulier zinvol zijn:

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

Indexstrategieën voor hiërarchische gegevens

Er zijn twee strategieën voor het indexeren van hiërarchische gegevens:

Diepte-eerst

In een diepte-eerste index worden de rijen in een substructuur bij elkaar opgeslagen. Alle werknemers die rapporteren via een manager, worden bijvoorbeeld opgeslagen in de buurt van de record van hun managers.

In een diepte-eerste index zijn alle knooppunten in de subboomstructuur van een knooppunt samengevoegd. Diepte-eerste indexen zijn daarom efficiënt voor het beantwoorden van query's over substructuren, zoals: "Alle bestanden in deze map en de bijbehorende submappen zoeken"
Breedte-eerst

In een breedte-eerste index worden de rijen per niveau van de hiërarchie gezamenlijk opgeslagen. De records van werknemers die rechtstreeks aan dezelfde manager rapporteren, worden bijvoorbeeld bij elkaar opgeslagen.

In een breedte-eerst index zijn alle directe onderliggende elementen van een knooppunt gegroepeerd. Breedte-eerste indexen zijn daarom efficiënt voor het beantwoorden van query's over directe kinderen, zoals: "Zoek alle werknemers die rechtstreeks rapporteren aan deze manager"

Of u nu diepte-eerst, breedte-eerst of beide wilt hebben en welke als clustersleutel gebruikt moet worden (indien van toepassing), hangt af van de relatieve waarde van de bovengenoemde typen query's en de relatieve waarde van SELECT versus DML-bewerkingen. Zie Zelfstudie: Het gegevenstype hierarchyid gebruiken voor een gedetailleerd voorbeeld van indexeringsstrategieën.

Indexen maken

De methode GetLevel() kan worden gebruikt om een breedte van de eerste volgorde te creëren. In het volgende voorbeeld worden zowel breedte-als diepte-eerste indexen gemaakt:

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

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase die u kunt downloaden van de startpagina van Microsoft SQL Server Samples en Community Projects .

Basisvoorbeeld

Het volgende voorbeeld is opzettelijk simplistisch om u te helpen aan de slag te gaan. Maak eerst een tabel voor het opslaan van enkele geografische gegevens.

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

Voeg nu gegevens in voor sommige continenten, landen/regio's, staten en steden.

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');

Selecteer de gegevens en voeg een kolom toe die de Level-gegevens omzet in een tekstwaarde die gemakkelijk te begrijpen is. Met deze query wordt het resultaat ook gesorteerd op het gegevenstype hierarchyid .

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

Hier is het resultatenoverzicht.

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

De hiërarchie heeft een geldige structuur, ook al is deze niet intern consistent. Bahia is de enige staat. Het verschijnt in de hiërarchie op gelijke positie met de stad Brasilia. Op dezelfde manier heeft McMurdo Station geen ouder land/regio. Gebruikers moeten bepalen of dit type hiërarchie geschikt is voor hun gebruik.

Voeg nog een rij toe en selecteer de resultaten.

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];

Dit demonstreert meer mogelijke problemen. Kyoto kan als niveau /1/3/1/ worden ingevoegd, ook al is er geen onderliggend niveau /1/3/. En zowel Londen als Kyoto hebben dezelfde waarde voor de hiërarchieid. Opnieuw moeten gebruikers bepalen of dit type hiërarchie geschikt is voor hun gebruik en blokwaarden blokkeren die ongeldig zijn voor hun gebruik.

Deze tabel heeft ook niet de bovenkant van de hiërarchie '/'gebruikt. Het is weggelaten omdat er geen gemeenschappelijke voorouder van alle continenten is. U kunt er een toevoegen door de hele planeet toe te voegen.

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

Migreren van ouder/kind naar hiërarchie-id

De meeste bomen worden weergegeven met behulp van ouder/kind. De eenvoudigste manier om te migreren van een bovenliggende structuur naar een tabel met behulp van hierarchyid is door een tijdelijke kolom of een tijdelijke tabel te gebruiken om het aantal knooppunten op elk niveau van de hiërarchie bij te houden. Voor een voorbeeld van het migreren van een ouder/kind-tabel, zie les 1 van Tutorial: Het gegevenstype hierarchyid gebruiken.

Een boom beheren door gebruik te maken van hierarchyid

Hoewel een hierarchieid-kolom niet per se een boomstructuur vertegenwoordigt, kan een toepassing er gemakkelijk voor zorgen dat dit wel gebeurt.

Voer een van de volgende stappen uit bij het genereren van nieuwe waarden:
- Houd het laatste kindnummer in de ouderrij bij.
- Bereken het laatste kindelement. Om dit efficiënt te doen is een breedte-eerst-index nodig.
Dwing uniekheid af door een unieke index in de kolom te maken, mogelijk als onderdeel van een clusteringsleutel. Voer een van de volgende stappen uit om ervoor te zorgen dat unieke waarden worden ingevoegd:
- Detecteer schendingen van unieke sleutels en probeer opnieuw.
- Controleer de uniekheid van elk nieuw kindknooppunt en voeg het toe als onderdeel van een serialiseerbare transactie.

Voorbeeld van foutdetectie

In het volgende voorbeeld berekent de voorbeeldcode de nieuwe onderliggende EmployeeId waarde en detecteert vervolgens een sleutelschending en gaat terug naar INS_EMP markeerpunt om de EmployeeId waarde voor de nieuwe rij opnieuw te berekenen.

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

Voorbeeld van een serialiseerbare transactie

De Org_BreadthFirst index zorgt ervoor dat het bepalen van @last_child gebruikmaakt van een zoekopdracht binnen een bereik. Naast andere foutgevallen die een toepassing mogelijk wil controleren, geeft een dubbele sleutelschending na de invoeging aan dat er meerdere werknemers met dezelfde id moeten worden toegevoegd en daarom @last_child opnieuw moeten worden gecomputeerd. Met de volgende code wordt de nieuwe knooppuntwaarde binnen een serialiseerbare transactie berekend:

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;

Met de volgende code wordt de tabel gevuld met drie rijen en worden de resultaten geretourneerd:

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

EXECUTE AddEmp 0x, 'Sariya';
GO

EXECUTE AddEmp 0x58, 'Mary';
GO

SELECT * FROM Org_T2

Hier is het resultatenoverzicht.

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

Een boom afdwingen

De vorige voorbeelden laten zien hoe een toepassing ervoor kan zorgen dat een boom wordt onderhouden. Als u een boom wilt afdwingen met behulp van beperkingen, kan een berekende kolom die het bovenliggende knooppunt definieert, worden gemaakt met een vreemde-sleutelbeperking die terugverwijst naar de primaire-sleutel-ID.

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

Deze methode voor het afdwingen van een relatie heeft de voorkeur wanneer code die niet wordt vertrouwd om de hiërarchische structuur te onderhouden, directe DML-toegang tot de tabel heeft. Deze methode kan echter de prestaties verminderen omdat de beperking moet worden gecontroleerd op elke DML-bewerking.

Voorouders zoeken met behulp van de CLR

Een veelvoorkomende bewerking waarbij twee knooppunten in een hiërarchie worden gebruikt, is het vinden van de laagste gemeenschappelijke voorouder. Deze taak kan worden geschreven in Transact-SQL of CLR, omdat het type hiërarchie-id beschikbaar is in beide. CLR wordt aanbevolen omdat de prestaties sneller zijn.

Gebruik de volgende CLR-code om voorouders weer te geven en om de laagste gemeenschappelijke voorouder te vinden:

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;
    }
}

Als u de ListAncestor en CommonAncestor methoden in de volgende Transact-SQL voorbeelden wilt gebruiken, maakt u het DLL-bestand en maakt u de HierarchyId_Operations assembly in SQL Server door code uit te voeren die vergelijkbaar is met het volgende voorbeeld:

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

Voorouderen vermelden

Het maken van een lijst met voorouders van een knooppunt is een algemene bewerking, bijvoorbeeld om de positie in een organisatie weer te geven. Een manier om dit te doen, is door een tabelwaardefunctie te gebruiken met behulp van de HierarchyId_Operations eerder gedefinieerde klasse:

Transact-SQL gebruiken:

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

Voorbeeld van gebruik:

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

De laagste gemeenschappelijke voorouder zoeken

Maak met behulp van de HierarchyId_Operations eerder gedefinieerde klasse de volgende Transact-SQL functie om de laagste gemeenschappelijke bovenliggende voorouder te vinden die betrekking heeft op twee knooppunten in een hiërarchie.

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