Neuerungen in SQL Server 2008: Der HierarchyId-Datentyp

Möchte man hier­ar­chi­sche Daten able­gen - wie z.B. Organisationsstrukturen in Unternehmen, so war das Standard-Vorgehen dazu bis­her, 2 Felder zu ver­wen­den - zunächst die Id des eigent­li­chen Elements und in einem wei­te­ren Feld die Id des Elternknotens.

Damit las­sen sich Abfragen wie: "Wer ist direk­ter Vorgesetzter des Mitarbeiters X?" oder "Welche Mitarbeiter sind direkt dem Vorgesetzten Y unter­stellt?" recht ein­fach beant­wor­ten. Umfangreichere Aussagen wie: "Was sind alle direk­ten und indi­rek­ten Mitarbeiter des Managers X?" oder "Wer ist Ebene-3-Manager des Mitarbeiters Y?" sind damit schon schwie­ri­ger zu ermitteln.

Nun kommt der HierarchyId-Datentyp ins Spiel. Mit ihm kön­nen sol­che Zusammenhänge dar­ge­stellt und ein­fach abge­fragt wer­den. Als Besonderheit gilt es zu beach­ten, dass die­ser Datentyp als CLR-Datentyp imple­men­tiert ist, der aller­dings auch dann zur Verfügung steht, wenn CLR-Integration deak­ti­viert ist. Daraus resul­tie­rend lei­tet sich auch die Darstellung der Funktionen ab. Dazu aber spä­ter mehr. Zunächst möchte ich die oben gezeigte Struktur mit die­sem Datentyp auf­bauen. Dazu erstelle ich eine neue Tabelle.

Im nächs­ten Schritt wird diese mit Daten gefüllt.

Lässt man sich nun die Daten ein­mal anzei­gen, ergibt sich fol­gen­des Bild:

Man erkennt, dass die Daten vor­lie­gen - aller­dings ist OrgNode eine hexa­de­zi­male Darstellung, die nicht ganz so benut­zer­freund­lich ist. Dafür gibt es Abhilfe, denn die Methode ToString gibt die kano­ni­sche Darstellung des OrgNodes zurück:

Nun erkennt man die hier­ar­chi­schen Beziehungen schon besser.

Um die Vorteile auch kom­plett aus­nut­zen zu kön­nen, sollte man einen Blick auf die Methoden des HierarchyId-Datentyps werfen:

• GetRoot(): ermit­telt den Hauptknoten
• GetAncestor(n): ermit­telt den n-ten Vaterknoten
• GetDescendant(child1, child2): ermit­telt Kindknoten
• GetLevel(): ermit­telt die Hierarchieebene
• IsDescendantOf(): ermit­telt, ob eine hier­ar­chi­sche Beziehung (ist y Kind von x) zwi­schen 2 Knoten besteht
• ToString(): Konvertierung in die kano­ni­sche Form
• Parse(): Konvertierung aus der kano­ni­schen Form
• GetReparentedValue(): nütz­lich zum Umhängen von Teilstrukturen

Spalten des Datentyps HierarchyId ach­ten nicht selbst auf die Eindeutigkeit. Aus die­sem Grund ist es sinn­voll, einen ein­deu­ti­gen Index auf die Spalte zu legen. Je nach Anwendungsfall unter­schei­det man hier Depth-First-Index und Breadth-First-Index. Beim Depth-First-Index wird zuerst in die Tiefe gegan­gen - also zuerst alle Kindelemente des Knotens 1 indi­ziert, bevor zu Knoten 2 über­ge­gan­gen wird. Beim Breadth-First-Index wird Ebene für Ebene indiziert.

Das Anlegen eines Depth-First-Index ist recht tri­vial - dazu indi­ziert man ein­fach eine bestehende HierarchyId-Spalte.

Für die Anlage eines Breadth-First-Indexes benö­tigt man noch eine zusätz­li­che Spalte in der Tabelle, in der der Level per­sis­tiert und die dann in den Index mit ein­be­zo­gen wird.