Working with hierarchyid Data
Ten temat zawiera informacje o typowych działań wykonywanych w zarządzaniu i badanie drzewa hierarchicznego danych.
W tym temacie.
Zarządzanie hierarchyid z za pomocą drzewa
Wymuszanie na drzewie
Przykład przy użyciu środowiska CLR
Przenoszenie poddrzewa
Zarządzanie hierarchyid z za pomocą drzewa
Chociaż hierarchyid kolumna nie reprezentuje drzewo, aplikacji mogą łatwo upewnij się, że obsługuje.
Podczas generowania nowych wartości, wykonaj jedną z następujących czynności:
Przechowywania informacji o ostatnim podrzędność numer wiersza nadrzędnego.
Obliczyć ostatni element podrzędność.W ten sposób efektywnie wymaga indeksu pierwszego szerokość.
Wymusić unikatowość przez utworzenie indeks unikatowy dla kolumna, prawdopodobnie jako część klucz klastrowania.Aby upewnić się, są wstawiane unikatowych wartości, wykonaj jedną z następujących czynności:
Określania unikatowość każdego nowego węzła podrzędnego i wstaw go, w ramach transakcji możliwy do serializacji.
wykrywać awarii naruszenie unikatowego klucz i spróbuj ponownie.
Przykład za pomocą wykrywania błędów
W poniższym przykładzie przykładowy kod oblicza nowe podrzędność EmployeeId wartość, a następnie wykrywa naruszenia klucz i powraca do INS_EMP znacznik, aby Przelicz EmployeeId wartość dla nowego wiersza:
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
Przykład za pomocą możliwy do serializacji transakcji
The Org_BreadthFirst index insures determining @last_child is a zakres seek.Oprócz innych przypadkach błąd aplikacji może być sprawdzenie duplikatów naruszenie klucz po insert wskazuje próba dodania wielu pracowników o tym samym identyfikatorze i w związku z tym @ last_child musi być przeliczane.W poniższym kodzie użyto możliwy do serializacji transakcji i indeksu pierwszego szerokość do obliczenia nowej wartości 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 wypełnia 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
Here is the result set.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
.gif "Arrow icon used with Back to Top link")
Powrót do początku
Wymuszanie na drzewie
Powyższe przykłady ilustrują, w jaki sposób aplikacji mogą zapewnić drzewa jest przechowywana.Aby wymusić drzewa, za pomocą ograniczeń, kolumna obliczana, definiujący nadrzędnego dla każdego węzła mogą być tworzone za pomocą ograniczenia na 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, aby zachować hierarchiczną drzewa ma bezpośredni dostęp DML do tabela.Ta metoda może zmniejszyć wydajność, ponieważ ograniczenia, należy sprawdzić w każdej operacji DML.
Powrót do początku
Przykład przy użyciu środowiska CLR
Znajdowanie najmniejszej wspólnej element nadrzędny jest typowych operacji związanych z dwóch węzłów w hierarchii.Może to być zapisany przy użyciu jednego Transact-SQL lub CLR, ponieważ hierarchyid Typ jest dostępny w obu. Środowisko CLR jest zalecane, ponieważ wydajność będzie szybsze.
Aby znaleźć listę obiektów nadrzędnych i znajdowanie najmniejszej wspólnej element nadrzędny, należy użyć następującego kodu CLR:
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 and CommonAncestor następujące metodTransact-SQL przykłady, tworzyć biblioteki i utworzyć HierarchyId_Operations wirtualny plik dziennika w SQL Server przez wykonaniem kodu, podobny do następującego:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Powrót do początku
Wyświetlanie listy obiekty nadrzędne
Tworzenie listy obiektów nadrzędnych węzła jest typowych operacji dla wystąpienie do wyświetlania pozycji w organizacji.Jednym ze sposobów robić to za pomocą-wycenione funkcja tabela przy użyciu HierarchyId_Operations klasy zdefiniowanej powyżej:
Za pomocą 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 najniższy wspólny element nadrzędny
Za pomocą HierarchyId_Operations klasy zdefiniowanej powyżej, utwórz następujące Transact-SQL funkcja Wyszukiwanie najniższy wspólny 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) ;
Wynikowy węzeł jest/1/1 /
Powrót do początku
Przenoszenie poddrzewa
Inna operacja wspólne jest przeniesienie poddrzewa.Poniższa procedura ma poddrzewa z @ oldMgr i czyni go (w tym @ 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.Reparent(@nold, @nnew)
WHERE @nold.IsDescendant(OrgNode) = 1 ;
COMMIT TRANSACTION
END ;
GO