Hierarchische Daten (SQL Server)

Gilt für:SQL Server Azure SQL-Datenbank Verwaltete Azure SQL-Instanz SQL-Datenbank in Microsoft Fabric

Der integrierte Datentyp hierarchyid vereinfacht das Speichern und Abfragen hierarchischer Daten. hierarchyid ist für die Darstellung von Bäumen optimiert, die die häufigste Art hierarchischer Daten sind.

Hierarchische Daten sind definiert als Satz von Datenelementen, die durch hierarchische Beziehungen miteinander verbunden sind. Hierarchische Beziehungen sind vorhanden, wenn ein Datenelement einem anderen Element übergeordnet ist. Beispiele für die hierarchischen Daten, die im Allgemeinen in Datenbanken gespeichert werden, umfassen folgende Artikel:

Eine Organisationsstruktur
Ein Dateisystem
Eine Gruppe von Aufgaben in einem Projekt.
Eine Taxonomie sprachlicher Termini
Ein Diagramm der Links zwischen Webseiten

Mit dem Datentyp hierarchyid können Sie Tabellen mit einer hierarchischen Struktur erstellen oder die hierarchische Struktur der Daten an einem anderen Speicherort beschreiben. Verwenden Sie die hierarchyid-Funktionen in Transact SQL, um hierarchische Daten abzufragen und zu verwalten.

Schlüsseleigenschaften

Ein Wert des hierarchyid -Datentyps stellt eine Position in einer Strukturhierarchie dar. Werte des Typs hierarchyid verfügen über die folgenden Eigenschaften:

Extrem kompakt

Die durchschnittliche Anzahl an Bits, die erforderlich sind, um einen Knoten in einem Baum mit n Knoten darzustellen, hängt vom durchschnittlichen Fanout (der durchschnittlichen Anzahl von Kindern eines Knotens) ab. Bei kleinen Fan-outs (0–7) beträgt die Größe etwa $6log{A}{n}$ Bit, wobei A der durchschnittliche Fan-out ist. Ein Knoten in einer 100.000 Leute umfassenden Organisationshierarchie mit durchschnittlich sechs Verzweigungen benötigt etwa 38 Bit. Dieser Wert wird bei der Speicherung auf 40 Bit oder 5 Byte aufgerundet.
Vergleiche erfolgen in Tiefensuchreihenfolge

Gegeben zwei hierarchyid-Werte a und b, bedeutet a < b, dass a bei einer Tiefensuche im Baum vor b kommt. Indizes für hierarchyid-Datentypen sind in Tiefensuchreihenfolge angeordnet, und Knoten, die bei einer Tiefensuche nahe beieinander liegen, werden auch nahe beieinander gespeichert. Die untergeordneten Datensätze eines Datensatzes werden beispielsweise neben diesem Datensatz gespeichert.
Unterstützung für beliebige Einfügungen und Löschungen

Mithilfe der Methode GetDescendant (Datenbank-Engine) ist es jederzeit möglich, einen Geschwisterknoten rechts oder links von einem beliebigen Knoten oder zwischen zwei Geschwisterknoten zu erzeugen. Die Vergleichseigenschaft bleibt auch dann gewahrt, wenn eine beliebige Anzahl von Knoten in die Hierarchie eingefügt oder aus ihr gelöscht wird. Die meisten Einfüge- und Löschoperationen behalten die Kompaktheitseigenschaft bei. Einfügungen zwischen zwei Knoten erzeugen jedoch hierarchyid-Werte in einer etwas weniger komprimierten Darstellung.

Limitations

Für den hierarchyid -Datentyp gelten folgende Einschränkungen:

Eine Spalte des Typs hierarchyid stellt nicht automatisch eine Baumstruktur dar. Es ist Aufgabe der Anwendung, hierarchyid -Werte so zu erstellen und zuzuweisen, dass die gewünschte Beziehung zwischen Zeilen anhand der Werte zu erkennen ist. Einige Anwendungen können eine Spalte vom Typ hierarchyid aufweisen, der den Speicherort in einer Hierarchie angibt, die in einer anderen Tabelle definiert ist.
Es ist Aufgabe der Anwendung, die Parallelität zu verwalten, indem sie geeignete hierarchyid -Werte generiert und zuweist. Es gibt keine Garantie dafür, dass die hierarchyid-Werte in einer Spalte eindeutig sind, es sei denn, die Anwendung verwendet eine Unique-Key-Einschränkung oder erzwingt die Eindeutigkeit selbst durch ihre eigene Logik.
Hierarchische, durch hierarchyid-Werte dargestellte Beziehungen werden nicht wie Fremdschlüsselbeziehungen durchgesetzt. Es ist möglich und manchmal auch angemessen, eine hierarchische Beziehung herzustellen, in der das Element A dem Element B untergeordnet ist, um dann A zu löschen, wodurch B mit einer Beziehung zu einem nicht mehr vorhandenen Datensatz verbleibt. Wenn dieses Verhalten inakzeptabel ist, muss die Anwendung vor dem Löschen übergeordneter Elemente nach untergeordneten Elementen suchen.

Wann Alternativen zu hierarchyid zu verwenden sind

Zur Darstellung hierarchischer Daten gibt es zwei Alternativen zu hierarchyid . Diese sind:

Parent/child
XML

hierarchyid ist diesen Alternativen im Allgemeinen überlegen. Jedoch gibt es bestimmte, in diesem Artikel aufgelistete Situationen, in denen diese Alternativen wahrscheinlich überlegen sind.

Parent/child

Beim Ansatz mit über- und untergeordneten Elementen enthält jede Zeile einen Verweis auf das übergeordnete Element. Die folgende Tabelle definiert eine typische Tabelle, die die Eltern- und Kindzeilen in einer Eltern-/Kind-Beziehung enthält:

USE AdventureWorks2022;
GO

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

Vergleich von über- und untergeordneten Elementen mit hierarchyid bei allgemeinen Vorgängen:

Unterstrukturabfragen sind mit hierarchyid signifikant schneller.
Abfragen nach direkten Nachfolgern sind mit hierarchyid etwas langsamer.
Das Verschieben von Nicht-Blattknoten ist mit hierarchyid langsamer.
Das Einfügen von Nicht-Blattknoten sowie das Einfügen oder Verschieben von Blattknoten weist bei hierarchyid die gleiche Komplexität auf.

Über-/Unterordnung könnte vorteilhafter sein, wenn die folgenden Bedingungen vorliegen:

Die Größe des Schlüssels ist wichtig. Bei gleicher Anzahl von Knoten ist ein hierarchyid-Wert genauso groß oder größer als ein Wert aus der Integer-Familie (smallint, int, bigint). Dies ist nur in seltenen Fällen ein Grund, eine Eltern-/Kind-Struktur zu verwenden, denn hierarchyid weist eine deutlich bessere E/A-Lokalität und CPU-Komplexität auf als die Common Table Expressions, die bei Verwendung einer Eltern-/Kind-Struktur erforderlich sind.
Abfragen erstrecken sich selten über Abschnitte der Hierarchie. Eine Abfrage bezieht sich mit anderen Worten in der Regel auf einen bestimmten Punkt in der Hierarchie. In diesen Fällen ist die gemeinsame Platzierung nicht entscheidend. Beispielsweise ist eine Eltern-/Kind-Beziehung besser geeignet, wenn die Organisationstabelle nur zur Lohn- und Gehaltsabrechnung für einzelne Mitarbeiter verwendet wird.
Nicht-Blatt-Teilbäume werden häufig verschoben, und die Leistung ist dabei von großer Bedeutung. In einer Eltern-/Kind-Darstellung wirkt sich das Verschieben einer Zeile innerhalb einer Hierarchie nur auf eine einzelne Zeile aus. Die Änderung der Position einer Zeile in einer hierarchyid -Struktur betrifft n Zeilen, wobei n die Zahl der Knoten angibt, die in der Teilstruktur verschoben werden.

Wenn Teilbäume ohne Blätter häufig verschoben werden und die Leistung eine wichtige Rolle spielt, die meisten Verschiebungen jedoch auf einer klar definierten Ebene der Hierarchie stattfinden, sollten Sie erwägen, die oberen und unteren Ebenen in zwei Hierarchien aufzuteilen. Dadurch beschränken sich alle Verschiebungen auf Blattebenen der höheren Hierarchie. Betrachten Sie beispielsweise eine Hierarchie der von einem Dienst gehosteten Websites. Websites enthalten viele auf hierarchische Weise angeordnete Seiten. Gehostete Sites können an einen anderen Ort innerhalb der Site-Hierarchie verschoben werden, jedoch werden die untergeordneten Seiten nur selten neu angeordnet. Dies könnte wie folgt dargestellt werden:
```
CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO
```

XML

Ein XML-Dokument ist eine Baumstruktur, daher kann eine einzige Instanz eines XML-Datentyps eine vollständige Hierarchie repräsentieren. Wenn in SQL Server ein XML-Index erstellt wird, werden intern hierarchyid-Werte verwendet, welche die Position in der Hierarchie angeben.

Die Verwendung des XML-Datentyps kann überlegen sein, wenn alle folgenden Punkte zutreffen:

Es wird immer die vollständige Hierarchie gespeichert und abgerufen.
Die Daten werden von der Anwendung im XML-Format verarbeitet.
Prädikatsuchen sind äußerst eingeschränkt und nicht leistungskritisch.

Wenn eine Anwendung zum Beispiel mehrere Organisationen verfolgt, immer die gesamte Organisationshierarchie speichert und abruft sowie keine einzelne Organisation abfragt, dann könnte eine Tabelle der folgenden Form sinnvoll sein:

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

Indexstrategien für hierarchische Daten

Für die Indizierung hierarchischer Daten gibt es zwei Strategien:

Depth-first

Ein Tiefenindex speichert die Zeilen eines Teilbaums eng beieinander. Zum Beispiel werden alle Mitarbeiter, die einem Manager unterstellt sind, nahe dem Datensatz ihres Managers gespeichert.

In einem tiefenorientierten Index sind alle Knoten im Teilbaum eines Knotens beieinander angeordnet. Daher sind Tiefensuchindizes besonders effizient bei der Abfrage von Teilstrukturen, etwa bei Abfragen des Typs: „Ermittle alle Dateien in diesem Ordner und seinen Unterordnern“
Breadth-first

Bei einem breitenorientierten Index werden die Zeilen jeder Ebene der Hierarchie zusammen gespeichert. Zum Beispiel werden die Datensätze aller Mitarbeiter, die direkt einem bestimmten Manager berichten, nahe beieinander gespeichert.

In einem Breitenindex sind alle direkten untergeordneten Knoten eines Knotens gemeinsam angeordnet. Daher sind Breitensuchindizes besonders effizient bei der Abfrage von unmittelbar untergeordneten Elementen, etwa bei Abfragen des Typs: „Ermittle alle Angestellten, die direkt diesem Manager berichten“

Ob Sie Tiefensuche, Breitensuche oder beides verwenden und welches davon Sie gegebenenfalls als Clusteringschlüssel festlegen, hängt von der relativen Wichtigkeit der oben genannten Abfragetypen und von der relativen Wichtigkeit von SELECT gegenüber DML-Vorgängen ab. Ein ausführliches Beispiel zu Indizierungsstrategien finden Sie unter Tutorial: Using the hierarchyid Data Type.

Erstellen von Indizes

Die Methode „GetLevel()“ kann verwendet werden, um eine Breitensuchreihenfolge zu erstellen. Im folgenden Beispiel werden sowohl Indizes für die Breitensuche als auch für die Tiefensuche erstellt:

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

Examples

Die Codebeispiele in diesem Artikel verwenden die AdventureWorks2025- oder AdventureWorksDW2025 Beispieldatenbank, die Sie von der Microsoft SQL Server Samples and Community Projects Homepage herunterladen können.

Einfaches Beispiel

Das folgende Beispiel wurde absichtlich einfach gehalten, um Ihnen die ersten Schritte zu erleichtern. Erstellen Sie zunächst eine Tabelle, in der einige geografischen Daten gespeichert werden sollen.

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

Fügen Sie nun Daten für einige Kontinente, Länder/Regionen, Bundesstaaten und Städte ein.

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

Wählen Sie die Daten aus, und fügen Sie eine Spalte hinzu, die die Level-Daten in einen leicht verständlichen Textwert umwandelt. Mit dieser Abfrage wird das Ergebnis auch nach dem hierarchyid -Datentyp sortiert.

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

Hier sehen Sie das Ergebnis.

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

Die Hierarchie verfügt über eine gültige Struktur, obwohl sie intern nicht konsistent ist. Bahia ist der einzige Bundesstaat. Es erscheint in der Hierarchie auf derselben Ebene wie die Stadt Brasilia. Entsprechend wird auch für McMurdo Station kein/e übergeordnete/s Land/Region angegeben. Die Benutzer müssen entscheiden, ob dieser Hierarchietyp für ihre Zwecke geeignet ist.

Fügen Sie eine weitere Zeile hinzu, und wählen Sie die Ergebnisse aus.

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

Dadurch werden weitere potenzielle Probleme deutlich. Kyoto kann als Ebene /1/3/1/ eingefügt werden, obwohl keine übergeordnete Ebene /1/3/vorhanden ist. Darüber hinaus verfügen sowohl London als auch Kyoto für hierarchyidüber denselben Wert. Auch in diesem Fall müssen die Benutzer entscheiden, ob dieser Hierarchietyp für sie geeignet ist und Werte blockieren, die nicht brauchbar sind.

Auch in dieser Tabelle wurde die oberste Hierarchieebene '/' nicht verwendet. Es wurde weggelassen, weil die Kontinente kein gemeinsames übergeordnetes Element haben. Sie können eines hinzufügen, indem Sie den ganzen Planeten hinzufügen.

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

Migration von parent/child zu hierarchyid

Die meisten Bäume werden durch Eltern-/Kind-Beziehungen dargestellt. Der einfachste Weg, von einer Parent-/Child-Struktur zu einer Tabelle zu migrieren, die hierarchyid verwendet, besteht darin, eine temporäre Spalte oder eine temporäre Tabelle zu verwenden, um die Anzahl der Knoten auf jeder Ebene der Hierarchie nachzuverfolgen. Ein Beispiel für die Migration einer über- und untergeordneten Tabelle finden Sie in Lektion 1 von Tutorial: Verwenden des hierarchyid-Datentyps.

Verwalten einer Struktur mit hierarchyid

Obwohl eine hierarchyid-Spalte nicht unbedingt eine Baumstruktur darstellt, kann eine Anwendung leicht sicherstellen, dass sie dies tut.

Führen Sie einen der folgenden Schritte durch, wenn Sie neue Werte erstellen:
- Halten Sie die Nummer des letzten untergeordneten Elements in der übergeordneten Zeile fest.
- Berechnen Sie das letzte untergeordnete Element. Für die effiziente Ausführung dieser Berechnung ist ein Breitensuchindex erforderlich.
Setzen Sie Eindeutigkeit durch, indem Sie für die Spalte, vielleicht als Teil eines Gruppierungsschlüssels, einen eindeutigen Index erstellen. Um sicherzustellen, dass eindeutige Werte eingefügt werden, führen Sie einen der folgenden Schritte durch:
- Erkennen Sie Verletzungen eines eindeutigen Schlüssels, und versuchen Sie es erneut.
- Prüfen Sie, ob jeder neue untergeordnete Knoten eindeutig ist, und fügen Sie ihn im Rahmen einer serialisierbaren Transaktion ein.

Beispiel für Fehlererkennung

Im folgenden Beispiel berechnet der Beispielcode den neuen Wert des untergeordneten Elements EmployeeId, erkennt dann eine Schlüsselverletzung und kehrt zur Markierung INS_EMP zurück, um den Wert EmployeeId für die neue Zeile erneut zu berechnen:

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

Beispiel für eine serialisierbare Transaktion

Der Org_BreadthFirst-Index stellt sicher, dass die Ermittlung von @last_child eine Bereichssuche verwendet. Zusätzlich zu anderen Fehlerfällen, die eine Anwendung möglicherweise prüfen möchte, zeigt eine Duplicate-Key-Verletzung nach dem Einfügen an, dass versucht wurde, mehrere Mitarbeiter mit derselben ID hinzuzufügen, und daher muss @last_child neu berechnet werden. Mit dem folgenden Code wird der neue Knotenwert in einer serialisierbaren Transaktion berechnet:

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;

Im folgenden Code wird die Tabelle mit drei Zeilen aufgefüllt. Anschließend werden die Ergebnisse zurückgegeben:

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 sehen Sie das Ergebnis.

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

Baum erzwingen

Die vorherigen Beispiele veranschaulichen, wie eine Anwendung sicherstellen kann, dass ein Baum beibehalten wird. Um eine Baumstruktur mithilfe von Einschränkungen zu erzwingen, kann eine berechnete Spalte erstellt werden, die den übergeordneten Knoten jedes Knotens definiert und über eine Fremdschlüsseleinschränkung auf die Primärschlüssel-ID zurückverweist.

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

Diese Methode der Durchsetzung einer Beziehung ist dann vorzuziehen, wenn Code, dem bezüglich der Bewahrung der hierarchischen Struktur nicht vertraut werden kann, über direkten DML-Zugriff auf die Tabelle verfügt. Diese Methode könnte jedoch die Leistung reduzieren, da die Einschränkung bei jedem DML-Vorgang geprüft werden muss.

Suchen von Vorgängern mit CLR

Ein allgemeiner Vorgang, der zwei Knoten einer Hierarchie betrifft, ist die Ermittlung des kleinsten gemeinsamen Vorgängers. Diese Aufgabe kann in Transact SQL oder CLR geschrieben werden, weil der hierarchyid-Typ in beiden verfügbar ist. CLR wird empfohlen, da es eine bessere Leistung bietet.

Verwenden Sie den folgenden CLR-Code, um die Vorgänger aufzulisten und den kleinsten gemeinsamen Vorgänger zu ermitteln:

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

Um die Methoden ListAncestor und CommonAncestor in den folgenden Transact SQL-Beispielen verwenden zu können, müssen Sie die DLL und die HierarchyId_Operations-Assembly in SQL Server erstellen, indem Sie Code ähnlich dem folgenden Beispiel ausführen:

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

Vorgänger auflisten

Das Erstellen einer Liste der Vorfahren eines Knotens ist eine gängige Operation, beispielsweise um die Position innerhalb einer Organisation anzuzeigen. Eine Möglichkeit dazu bietet die Verwendung einer Tabellenwertfunktion mithilfe der zuvor definierten HierarchyId_Operations-Klasse:

Verwenden von Transact-SQL:

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

Beispiel für die Verwendung:

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

Finden Sie den niedrigsten gemeinsamen Vorfahren

Erstellen Sie mithilfe der zuvor definierten HierarchyId_Operations-Klasse die folgende Transact SQL-Funktion, die den kleinsten gemeinsamen Vorgänger zweier Knoten in einer Hierarchie ermittelt:

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