Steffen über Cloud, Azure und Datenbanken

Steffen Krause - Cloud Solution Architect

SQL Server 2008 (3): Hierarchische Daten und HierarchyID

SQL Server 2008 (3): Hierarchische Daten und HierarchyID

  • Comments 3
  • Likes

Eine weitere Neuerung von SQL Server 2008 ist ein optimierter Datentyp namens hierarchyid für die Verarbetung von hierarchischen und Baum-Strukturen. Solche Strukturen treten zum Beispiel in Organisationsstruturen (wer ist Manager von wem?), in Taxonomien, in Produkthierarchien und an vielen anderen Orten auf.

Bisher hat man solche Strukturen vor allem mit Hilfe von 2 getrennten Feldern aufgebaut - das eine ist die ID, das andere die ID des Vaterknotens. Das ist zwar gut, um direkte Kinder eines Knotens oder den direkten Vater darzustellen, aber alle weitergehenden Abfragen sind schwer darstellbar, wie zum Beispiel "Alle direkten und indirekten Mitarbeiter eines Managers" oder "Welches ist der Ebene 2 Manager eines gegebenen Mitarbeiters". Solche Abfragen sind ebenso wie die Auflistung eines Baumes nach einer gegebenen Strategie (Tiefe zuerst oder Breite zuerst) mit dem Hierarchyid-Datentyp sehr einfach zu erstellen.

Der Hierarchyid-Datentyp selbst ist ein Binärwert, dessen Größe von der Größe der Hierarchie ahängt. Er hat zwei textliche Darstellungen: Zum einen die hexadezimale Darstellung, zum anderen eine sogenannte kanonische Darstellung, die die Reihenfolge der Kindknoten im Baum darstellt. Eine solche kanonische Darstellung ist /1/1/2/ Das bedeutet, der Knoten befindet sich auf der dritten Ebene an Ordnungsreihenfolge 2, ist Kind des Knoten mit Ordnungsreihenfolge 1 der 2. Ebene und so weiter. Ich habe hier Ordnugnsreihenfolge geschrieben, denn es ist notwendigerweise das zweite Kind von Knoten /1/1/. Werden nämlich später Knoten "dazwischengeschoben" oder gelöscht, so kann /1/1/2/ auf das erste oder vierte Kind von /1/1/ sein, das dritte wäre dann z.B. /1/1/1.3/. Man kann nämlich durch gezielte Operationen genau festlegen, dass man einen Knoten zwischen zwei andere schieben will.

Hierarchyid ist als CLR-Datentyp implementiert, das heißt, er verwendet eine .NET Assembly. Er steht aber immer zur Verfügung, auch wenn "CLR Integration" deaktiviert ist. Die Herkunft aus .NET erkennt man an den Abfrage- und Zugriffsfunktionen, die wie in der objektorientierten Programmierung üblich als Methoden auf ein Hierarchyid-Element angewendet werden und mit . getrennt hinter diesem stehen. So erhält man mit .GetLevel() die Hierarchieebene eines Elements. Mit .GetAncestor(i) bekommt man den Vater i Ebenen höher, mit .GetAncestor(1) also den direkten Vater. Die flexibelste Methode ist GetDescendant(h1, h2). Hiermit kann man sich nicht nur Kinder eines gegebenen Knotens geben lassen, sindern auch eine neue Hierarchyid, die zwischen zwei bereits vorhandenen Kindern liegt. Mit .Reparent(alterVater, neuerVater) kann man einen Teilbaum umhängen.

Um Hierarchyid-Abfragen effizient zu machen sollte man immer einen Index auf das hierarchyid-Feld erstellen. Dann können Teilbaumabfragen einfach durch Range-Scans auf den Index effizient von SQL Server beantwortetHierarchid arbeitet dabei mit einem "Tiefe zuerst"-Algorithmus. Das bedeutet, dass beim Baum zuerst in die Tiefe gegangen wird. Das ist die natürliche Reihenfolge des Hierarchid-Datentyps. Der Index hann dafür einfach auf das Hierarchid-Feld erstellt werden. Die Reihenfolge sieht dann also so aus:

/
/1/
/1/1/
/1/1/1/
/1/1/2/
/1/2/
/1/3/
/1/3/1/
/1/3/2/
/1/4/
/2/
...

Alternativ kann man auch einen Index für einen "Breite zuerst"-Index angelegt werden, Details dazu finden sich hier.

Anbei wie immer ein Beispielprojekt für die Verwendung, in diesem Fall habe ich das Tutorial in der Dokumentation so abgewandelt, dass ich eine Kopie der Employee-Tabelle aus AdventureWorks sowohl mit den vorhandenen Parent/Child-Beziehungen als auch mit einer Hierarchyid-Spalte ausgestattet habe, um den Vergleich zwischen beiden Methoden zu ermöglichen.

Das Projekt ist hier

Gruß,
Steffen

Comments
  • Auf dieser Seite möchte ich meine Ressourcen zu SQL Server 2008 zusammenfassen Meine Blog-Einträge: SQL

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment