Praca z hierarchyid danych
Ten temat zawiera informacje o typowych działań w zarządzaniu i badanie drzewa hierarchicznego danych.
W tym temacie
Zarządzanie hierarchyid przy użyciu drzewa
Wymuszanie drzewa
Przykład przy użyciu środowiska CLR
Przenoszenie poddrzewa
Zarządzanie hierarchyid przy użyciu drzewa
Chociaż hierarchyid kolumna nie reprezentuje drzewo, aplikacja łatwo można zagwarantować, że wykonuje.
Podczas generowania nowych wartości, wykonaj jedną z następujących czynności:
Zachowaj o ostatni numer wiersza nadrzędnego podrzędność.
Obliczyć ostatniego podrzędność.W ten sposób efektywnie wymaga indeksu pierwszego szerokość.
Wymusić unikatowość tworząc indeks unikatowy na kolumnie prawdopodobnie jako część klucz klastrowania.W celu zapewnienia unikatowej wartości są wstawiane, wykonaj jedną z następujących czynności:
Nowe ustalenia unikatowość każdego podrzędność węzeł wstawić w transakcji możliwy do serializacji.
Wykrywanie awarii naruszenie unikatowego klucz i ponów próbę.
Przykład za pomocą wykrywania błędów
W poniższym przykładzie, przykładowy kod oblicza nowy element podrzędność IDPracownika wartości, a następnie wykrywa wszelkie naruszenia klucz i zwraca do INS_EMP znacznik, aby Przelicz IDPracownika wartość dla nowego wiersza:
USE AdventureWorks2008R2;
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
Przykład przy użyciu transakcji serializować
Org_BreadthFirst indeks już określania @ last_child jest zakres wyszukiwania.Oprócz innych przypadkach błąd aplikacji warto sprawdzić zduplikowane naruszenie klucz po Wstaw wskazuje próba dodania wielu pracowników o tym samym identyfikatorze i dlatego @ last_child musi być przeliczane.Następujący kod używa serializować transakcji i indeks pierwszego szerokość do obliczenia wartości nowego węzła:
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 ;
Poniższy kod spowoduje wypełnienie tabela z trzech wierszy i zwraca wyniki:
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2
Oto zestaw wyników.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
.gif "Ikona strzałki używana z łączem Powrót na górę strony")
Powrót do początku
Wymuszanie drzewa
Powyższe przykłady ilustrują, jak aplikację można zapewnić utrzymane drzewa.Aby wymusić drzewa poprzez ograniczenia, kolumna obliczana definiuje nadrzędnego każdego węzła można tworzyć ograniczenia klucz obcy do identyfikatora klucz podstawowy.
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
Wymuszanie relacja ta metoda jest preferowana, jeżeli kod, który nie jest zaufany do utrzymania hierarchiczne drzewo ma bezpośredni DML dostępu do tabela.Ta metoda może zmniejszyć wydajność, ponieważ ograniczenia muszą być sprawdzone w każdym DML operacji.
Powrót do początku
Przykład przy użyciu środowiska CLR
Wspólne działania, obejmujący dwa węzły w hierarchii jest znalezienie najmniejszej wspólnej element nadrzędny.Mogą to być napisane w albo Transact-SQL lub CLR, ponieważ hierarchyid typu są dostępne zarówno.CLR jest zalecane, ponieważ wydajność będzie szybsze.
Należy użyć następującego kodu CLR znaleźć listę obiektów nadrzędnych i znajdowania najniższej element nadrzędny wspólne:
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;
}
}
Aby użyć ListAncestor i CommonAncestor metod w następujących Transact-SQL Przykłady, budowanie biblioteki DLL i utworzyć HierarchyId_Operations wirtualny plik dziennika w SQL Server przez wykonywanie kodu jest podobny do następującego:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Powrót do początku
Wyświetlanie listy obiektów nadrzędnych
Tworzenie listy przodków węzła jest operacją wspólne dla wystąpienie pokazać stanowisko w organizacji.Jednym ze sposobów jest za pomocą-wycenione funkcja tabela za pomocą HierarchyId_Operations klasy zdefiniowanej powyżej:
Using Transact-SQL:
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO
Przykład użycia:
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
Znajdowanie najmniejszej wspólnej nadrzędnego
Za pomocą HierarchyId_Operations klasy powyżej, utworzyć następujące Transact-SQL funkcja Wyszukiwanie najniższego wspólnego element nadrzędny obejmujące dwa węzły w hierarchii:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Przykład użycia:
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) ;
Węzeł wynikowy jest/1/1 /
Powrót do początku
Przenoszenie poddrzewa
Inna operacja wspólnej jest przenoszenie poddrzew.Poniższa procedura ma drzewo podrzędne @ oldMgr i czyni go (łącznie z @ oldMgr) poddrzewa @ 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 OrgNode.IsDescendant(@nold) = 1 ;
COMMIT TRANSACTION
END ;
GO