使用 hierarchyid 資料

本主題包含關於管理與查詢階層式資料樹狀結構之一般活動的資訊。

在此主題中

使用 hierarchyid 管理樹狀結構

強制執行樹狀結構

使用 CLR 的範例

移動子樹

使用 hierarchyid 管理樹狀結構

雖然 hierarchyid 資料行不需要代表樹狀結構，但是應用程式可以輕易地確認這就是樹狀結構。

• 產生新值時，請執行下列其中之一：

  • 追蹤父資料列中的最後一個子系號碼。

  • 計算最後一個子系。若要有效率地執行這個動作，需要使用廣度優先索引。

• 在資料行上建立唯一的索引 (也許是叢集索引鍵的一部份) 以強制執行唯一性。若要確保插入唯一的值，請執行下列其中之一：

  • 在序列化交易中判斷每個新子節點的唯一性，然後插入該子節點。

  • 偵測唯一的索引鍵違規失敗，然後重試。

使用錯誤偵測的範例

在下列範例中，範例程式碼會計算新子系的 EmployeeId 值，然後偵測任何索引鍵違規，並傳回 INS_EMP 標記，以便為新的資料列重新計算 EmployeeId 值：

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

使用序列化交易的範例

Org_BreadthFirst 索引可確定所判斷的 @last_child 為範圍搜尋。除了應用程式可能想要檢查的其他錯誤情況之外，插入後的重複索引鍵違規會指出新增多個具有相同識別碼之員工的嘗試，因此必須重新計算 @last_child。下列程式碼使用序列化交易與廣度優先索引來計算新的節點值：

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 ;

下列程式碼會使用三個資料列擴展資料表，然後傳回結果：

INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2

以下為結果集：

EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

回到頂端

強制執行樹狀結構

以上的範例說明應用程式可以如何確保樹狀結構的維護。若要透過條件約束強制使用樹狀結構，定義每個節點之父系的計算資料行可以使用傳回主要索引鍵識別碼的外部索引鍵條件約束建立。

CREATE TABLE Org_T3
(
   EmployeeId hierarchyid PRIMARY KEY,
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED  
      REFERENCES Org_T3(EmployeeId),
   LastChild hierarchyid, 
   EmployeeName nvarchar(50)
)
GO

不信任可以維護階層式樹狀結構的程式碼具有資料表的 DML 直接存取權時，最好使用這個方法強制關聯性。由於必須針對每個 DML 作業檢查條件約束，這個方法可能會降低效能。

回到頂端

使用 CLR 的範例

與階層中兩個節點相關的常見作業就是尋找最低通用上階。這可以寫入 Transact-SQL 或 CLR，因為這兩者都可以使用 hierarchyid 類型。因為效能將會更快，因此建議使用 CLR。

使用下列的 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;
    }
}

若要在下列 Transact-SQL 範例中使用 ListAncestor 和 CommonAncestor 方法執行類似如下的程式碼，請在 SQL Server 中組建 DLL 並建立 HierarchyId_Operations 組件：

CREATE ASSEMBLY HierarchyId_Operations 
FROM '<path to DLL>\ListAncestors.dll'
GO

回到頂端

列出上階

建立節點上階清單是一個常見的作業，例如，在組織中顯示位置。其中一個方法是，利用以上定義的 HierarchyId_Operations 類別，使用資料表值函數：

使用 Transact-SQL：

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

使用範例：

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

尋找最低通用上階

使用以上定義的 HierarchyId_Operations 類別建立下列的 Transact-SQL 函數，尋找與階層中兩個節點相關的最低通用上階：

CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO

使用範例：

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) ;

產生的節點是 /1/1/

回到頂端

移動子樹

另一個常見的作業是移動子樹。以下的程序使用 @oldMgr 的子樹，並讓其 (包括 @oldMgr) 成為 @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

請參閱

參考

hierarchyid (Transact-SQL)

概念

使用 hierarchyid 資料類型 (Database Engine)

Hierarchyid 資料類型方法參考

教學課程：使用 hierarchyid 資料類型