Arbeiten mit hierarchyid-Daten
Dieses Thema umfasst Informationen über allgemeine Aktivitäten bei der Verwaltung und der Abfrage einer hierarchischen Datenstruktur.
In diesem Thema
Verwalten einer Struktur mit hierarchyid
Durchsetzen einer Struktur
Beispiel für die Verwendung von CLR
Verschieben von Teilstrukturen
Verwalten einer Struktur mit 'hierarchyid'
Eine hierarchyid-Spalte muss zwar nicht notwendigerweise eine Struktur darstellen, jedoch kann eine Anwendung dies auf einfache Weise sicherstellen.
Führen Sie eine der folgenden Maßnahmen 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 eine der folgenden Maßnahmen durch:
Bestimmen Sie die Eindeutigkeit eines jeden neuen untergeordneten Knotens, und fügen Sie ihn in einer serialisierbaren Transaktion ein.
Spüren Sie Verletzungen des eindeutigen Indexes auf und wiederholen Sie den Vorgang.
Beispiel für Fehlererkennung
Der Code im folgenden Beispiel berechnet den EmployeeId-Wert des neuen untergeordneten Elements, und spürt dann eine Schlüsselverletzung auf, worauf er zur Markierung INS_EMP zurückkehrt, um den EmployeeId-Wert für die neue Zeile neu 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 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 @last_child mittels einer Bereichssuche ermittelt wird. Zusätzlich zu anderen Fehlerfällen könnte eine Anwendung prüfen, ob eine Verletzung aufgrund doppelter Schlüssel darauf hindeutet, dass versucht wurde, mehrere Angestellte mit der gleichen ID einzufügen, weshalb @last_child neu berechnet werden muss. Im folgenden Code werden eine serialisierbare Transaktion und ein Breitensuchindex verwendet, um den neuen Knotenwert zu berechnen:
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
UPDATE Org_T2
SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL)
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
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2
Dies ist das Resultset.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
.gif "Pfeilsymbol, dass mit dem Link \"Zurück zum Anfang\" verwendet wird")
Zurück zum Anfang
Durchsetzen einer Struktur
In den Beispielen oben wird veranschaulicht, wie eine Anwendung sicherstellen kann, dass eine Struktur gewahrt bleibt. Um eine Struktur mithilfe von Einschränkungen durchzusetzen, kann eine berechnete Spalte mit einer Fremdschlüsseleinschränkung für die Primärschlüssel-ID erstellt werden, die das übergeordnete Element jedes Knotens berechnet.
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
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 die Leistung reduzieren, da die Einschränkung bei jedem DML-Vorgang geprüft werden muss.
Zurück zum Anfang
Beispiel für die Verwendung von CLR
Ein allgemeiner Vorgang, der zwei Knoten einer Hierarchie betrifft, ist die Ermittlung des kleinsten gemeinsamen Vorgängers. Dies kann in Transact-SQL oder CLR geschrieben werden, weil der hierarchyid-Typ in beiden verfügbar ist. CLR wird empfohlen, da die Leistung höher ist.
Verwenden Sie den folgenden CLR-Code, um eine Liste der Vorgänger und den kleinsten gemeinsamen Vorgänger zu ermitteln:
using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
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.IsDescendant(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 ausführen:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Zurück zum Anfang
Auflisten von Vorgängern
Die Erstellung einer Liste von Vorgängern, um beispielsweise die Position innerhalb einer Organisation anzuzeigen, ist ein häufig vorkommender Vorgang. Eine Möglichkeit dazu bietet die Verwendung einer Tabellenwertfunktion mithilfe der oben 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 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
JOIN ListAncestors(@h) AS A
ON ED.OrgNode = A.Node
GO
Ermitteln des kleinsten gemeinsamen Vorgängers
Erstellen Sie mithilfe der oben 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
Beispiel für die Verwendung:
DECLARE @h1 hierarchyid, @h2 hierarchyid
SELECT @h1 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/
SELECT @h2 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/
SELECT OrgNode.ToString() AS LogicalNode, LoginID
FROM HumanResources.EmployeeDemo
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;
Der resultierende Knoten ist /1/1/
Zurück zum Anfang
Verschieben von Teilstrukturen
Ein anderer allgemeiner Vorgang ist das Verschieben von Teilstrukturen. Die Prozedur unten macht die Teilstruktur @oldMgr (einschließlich @oldMgr) zu einer Teilstruktur von @newMgr.
CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) )
AS
BEGIN
DECLARE @nold hierarchyid, @nnew hierarchyid
SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ;
SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL)
FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ;
UPDATE HumanResources.EmployeeDemo
SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew)
WHERE @nold.IsDescendant(OrgNode) = 1 ;
COMMIT TRANSACTION
END ;
GO