Hierarchyid veri ile çalışma
Bu konu, sıradüzensel veri ağacı sorgulama yönetme ve ortak faaliyetler hakkında bilgi içerir.
Bu konu içinde
Bir ağacı kullanarak hierarchyid yönetme
Bir ağaç zorlama
CLR kullanmaya örnek
Alt ağaçlar taşıma
Bir ağacı kullanarak hierarchyid yönetme
Ancak bir hierarchyid Sütun bir ağaç temsil etmez, bir uygulama, bunu yapar kolayca sağlayabilirsiniz.
Yeni değerler oluştururken, aşağıdakilerden birini yapın:
Üst satırdaki son alt numarasını izlemek.
Son alt hesaplamak.Bunu etkin bir etki derecesini ilk dizin gerektirir.
sütun, belki bir kümeleme anahtarının bir parçası olarak benzersiz dizin oluşturarak benzersizlik.Benzersiz değerler eklenir emin olmak için , aşağıdakilerden birini yapın:
Her yeni alt düğümü benzersizliğini belirleme ve, bir seri hale getirilebilir işleme ekleyin.
Benzersiz anahtar ihlali başarısızlıklarını saptayabilir ve yeniden deneyin.
Hata algılama kullanmaya örnek
Aşağıdaki örnekte, yeni alt örnek kod hesaplar. Employeeıd değer, herhangi bir anahtar ihlali algılar ve için veririns_emp için yeniden işaretçisiEmployeeıd değer için yeni bir satır:
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
Seri hale getirilebilir bir işlem kullanmaya örnek
The Org_BreadthFirst index insures determining @last_child is a aralık seek.Ek olarak diğer hata durumlarda, bir uygulama, ekleme, aynı kimliğe sahip birden fazla çalışanı ekleme denemesi belirtir, sonra bir yinelenen anahtar ihlali denetlemek isteyebilirsiniz ve bu nedenle @ last_child recomputed gerekir.Aşağıdaki kod yeni düğüm değerini hesaplamak için bir seri hale getirilebilir işlem ve bir etki derecesini ilk dizin kullanır:
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 ;
Aşağıdaki kod, üç satır içeren tabloyu doldurur ve sonuçlar döndürür:
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
Başa Dön
Bir ağaç zorlama
Yukarıdaki örneklerde, uygulamanın bir ağaç korunur nasıl emin olabilirsiniz gösterilmektedir.Ağacı üzerinden kısıtlamaları zorlamak için , her düğümün üst tanımlar, hesaplanan bir sütun yabancı anahtar kısıtlaması için geri birincil anahtar kimliği ile oluşturulabilir.
CREATE TABLE Org_T3
(
EmployeeId hierarchyid PRIMARY KEY,
ParentId AS EmployeeId.GetAncestor(1) PERSISTED
REFERENCES Org_T3(EmployeeId),
LastChild hierarchyid,
EmployeeName nvarchar(50)
)
GO
Hiyerarşik ağaç korumak için güvenilir olmayan kod tablo DML doğrudan erişimi varsa, bu ilişki zorlama yöntem tercih edilir.Kısıtlamayı her DML işlemi iade edilmesi gerekir, çünkü bu yöntem performansını düşürebilir.
Başa Dön
CLR kullanmaya örnek
Bir sıradüzeni içindeki iki düğüm ilgili genel bir işlem en düşük olan ortak üst bulmaktır.Bu da yazılabilir Transact-SQL veya, CLR çünkü hierarchyid türü her ikisi de kullanılabilir. CLR önerilir; çünkü, performans, daha hızlı olacaktır.
Liste öncüleri bulunmaya ve en düşük olan ortak üst bulmak için CLR şu kodu kullanın:
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;
}
}
Kullanılacak ListAncestor and CommonAncestor Aşağıdaki yöntemleriTransact-SQL örnekler, dll DOSYASıNı oluşturun ve oluşturma HierarchyId_Operations derlemedeSQL Server aşağıdakine benzer bir kod yürütmek yoluyla:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
Başa Dön
Öncüleri listeleniyor.
Düğümün öncüleri listesini oluşturma genel bir işlem örnek için olan bir kuruluş içindeki konumunu göstermek için.Bu tablo değerli fonksiyon kullanarak kullanarak, bunu yapmak, tek yönlü HierarchyId_Operations sınıfı, yukarıda tanımlanan:
Bu alternatif Transact-SQL:
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO
Örnek Kullanım:
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
En düşük olan ortak üst bulma
Kullanarak HierarchyId_Operations sınıfı, yukarıda tanımlanan aşağıdaki oluşturmaTransact-SQL işlev, bir hiyerarşi içinde iki düğüm ilgili en küçük ortak üst bulmak için:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Örnek Kullanım:
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) ;
Sonuç düğümü ise/1/1 /
Başa Dön
Alt ağaçlar taşıma
Başka bir genel işlem alt ağaçlar taşıma.Aşağıdaki yordam, alt ağaçta alır. @ oldMgr ve ( dahil yapar**@ oldMgr**) bir alt ağacı**@ 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