階層式資料 (SQL Server)

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Microsoft Fabric 中的 SQL 資料庫

內建 hierarchyid 資料類型讓儲存與查詢階層式資料更容易。 hierarchyid 已針對表示樹狀結構進行最佳化，而樹狀結構是最常見的階層式資料類型。

階層式資料的定義為一組資料項目，這些資料項目會依據階層式關聯性，彼此相關。階層式關聯性表示資料的一個項目是另一個項目的父代。通常儲存在資料庫的階層式資料範例包含下列項目：

組織結構
檔案系統
專案中的一組工作
語言詞彙的分類表
網頁之間的連結圖形

使用 hierarchyid 做為資料類型來建立具有階層式結構的資料表，或描述儲存在另一個位置的階層式資料結構。使用 Transact-SQL 中的 hierarchyid 函數來查詢及管理階層式資料。

關鍵屬性

hierarchyid 資料類型的值代表樹狀目錄階層中的位置。 hierarchyid 的值具有下列屬性：

極其緊湊

在具有 n 個節點的樹中，表示一個節點所需的平均位元數取決於平均扇出（即一個節點的平均子節點數）。對於較小的扇出數（0–7），其大小約為 $6log{A}{n}$ 位元，其中 A 為平均扇出數。在一個有 100,000 人、平均分支數為 6 的組織階層中，一個節點大約需要 38 位元。然後，這會四捨五入成 40 位元 (或 5 個位元組)，以便進行儲存。
比較是依深度優先順序進行的

給定兩個 hierarchyid 值 a 和 b，a < b 表示在樹狀結構的深度優先走訪中，a 先於 b。 hierarchyid 資料類型的索引採深度優先順序，而在深度優先走訪中彼此接近的節點，也會儲存在彼此相近的位置。例如，某筆記錄的子系會儲存在該記錄旁。
支援任意插入和刪除

使用 GetDescendant (資料庫引擎) 方法時，一律可以在任何指定節點的右側、任何指定節點的左側，或任何兩個同層節點之間產生同層節點。在階層中插入或刪除任意的節點數目時，比較屬性會保留下來。大部分的插入和刪除項目都會保留壓縮度屬性。不過，在兩個節點之間插入，會產生表示法稍微較不精簡的 hierarchyid 值。

Limitations

hierarchyid 資料類型具有下列限制：

hierarchyid 類型的資料行不會自動表示樹狀結構。應用程式負責決定是否要產生並指派 hierarchyid 值，以便讓資料列之間所需的關聯性反映在值中。有些應用程式可能有 hierarchyid 類型的資料行，表示在另一個資料表中定義之階層的位置。
應用程式負責管理產生與指派 hierarchyid 值的並行。除非應用程式使用唯一鍵條件約束，或透過自身的邏輯強制確保唯一性，否則資料行中的 hierarchyid 值並不保證是唯一的。
由 hierarchyid 值表示的階層關係，不會像外部鍵關係那樣被強制執行。可能會存在一種階層式關係，而且有時這樣做也是適當的：A 有一個子項 B，接著 A 被刪除，使 B 仍與一筆不存在的記錄保有關聯。如果這種行為無法接受，應用程式在刪除父項目之前，必須先查詢其後代項目。

何時使用 hierarchyid 的替代方案

代表階層式資料的兩個 hierarchyid 替代選項為：

Parent/child
XML

hierarchyid 通常優先於這些替代選項。但是，在本文中詳細介紹的特定情況下，替代方案可能更佳。

Parent/child

使用父/子方式時，每個資料列都包含一個對父項的參照。下表定義了在父/子關聯中用來包含父資料列和子資料列的典型資料表：

USE AdventureWorks2022;
GO

CREATE TABLE ParentChildOrg (
    BusinessEntityID INT PRIMARY KEY,
    ManagerId INT FOREIGN KEY REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName NVARCHAR(50)
);
GO

比較父/子和 hierarchyid 在常見作業中的表現：

使用 hierarchyid 進行子樹查詢時，速度明顯加快。
而使用 hierarchyid 進行直接下階查詢時，速度則稍慢。
使用 hierarchyid 移動非分葉節點時，速度比較慢。
使用 hierarchyid 時，插入非分葉節點以及插入或移動分葉節點，兩者的複雜度相同。

在下列情況下，父/子架構可能較佳：

鍵的大小至關重要。如果節點數目相同， hierarchyid 值會等於或大於整數系列 (smallint、 int、 bigint) 值。只有在少數情況下，這才是使用父/子結構的理由，因為與使用父/子結構時所需的通用資料表表達式相比，hierarchyid 的 I/O 區域性顯著更佳，CPU 複雜度也明顯較低。
查詢很少會跨階層中的不同區段進行查詢。換句話說，查詢通常只處理階層中的單一點。在這些情況下，是否位於同一位置並不重要。例如，當組織資料表僅用於處理個別員工的薪資時，父/子結構較佳。
非分葉的子樹會經常移動，因此效能非常重要。在父子式表示中，變更資料列在階層中的位置會影響單一資料列。在 hierarchyid 使用方式中變更資料列的位置會影響 n 個資料列，其中 n 是要移動之樹狀子目錄中的節點數目。

如果非葉節點子樹經常移動，而且效能很重要，但大多數移動都發生在階層中明確界定的某一層級，請考慮將較高層與較低層拆分成兩個階層架構。這會使所有移動都進入較高階層中的葉節點層級。例如，請考慮由服務主控之網站的階層。網站包含許多以階層方式排列的頁面。主控的網站可能會移到網站階層的其他位置，但是從屬的頁面很少會重新排列。這可能會透過下列方式表示：
```
CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO
```

XML

XML 文件是一個樹狀結構，因此，單一的 XML 資料類型執行個體可以代表一個完整的階層。在 SQL Server 中建立 XML 索引時，hierarchyid 值會用在內部，以表示在階層中的位置。

如果以下所有狀況成立，使用 XML 資料類型可能比較好：

完整的階層一律會被儲存及取出。
應用程式使用的資料為 XML 格式。
述詞搜尋會受到相當的限制，而且效能並不重要。

例如，如果應用程式追蹤多個組織，務必儲存與擷取完整的組織階層，而且不要在單一組織中查詢，下列表單的資料表才可能有意義：

CREATE TABLE XMLOrg (
    Orgid INT,
    Orgdata XML
);
GO

階層式資料的索引策略

編製階層式資料索引有兩種策略：

Depth-first

深度優先索引會將子樹中的資料列儲存在彼此相近的位置。例如，所有隸屬於某位經理的員工都會儲存在其經理紀錄附近。

在深度優先索引中，某個節點的子樹中的所有節點都會集中排列在一起。因此，深度優先的索引在回應關於子樹的查詢 (例如，「尋找此資料夾及其子資料夾中的所有檔案」) 時很有效率
Breadth-first

廣度優先索引會將階層中每個層級的資料列一起儲存。例如，直接向同一位經理報告的員工，其記錄會儲存在彼此相近的位置。

在廣度優先索引中，某個節點的所有直接子節點都會集中在一起。因此，廣度優先的索引在回應關於下層子系的查詢 (例如，「尋找直接回報給此經理的所有員工」) 時很有效率

不論是讓深度優先、廣度優先，或是兩者，還是那個要產生叢集索引鍵 (如果有的話)，都取決於上述查詢類型的相對重要性，以及 SELECT 和 DML 作業的相對重要性。如需索引策略的詳細範例，請參閱＜ Tutorial: Using the hierarchyid Data Type＞。

建立索引

GetLevel() 方法可以用來建立廣度優先排序。在下列範例中，會同時建立廣度優先和深度優先的索引：

USE AdventureWorks2022;
GO

CREATE TABLE Organization (
    BusinessEntityID HIERARCHYID,
    OrgLevel AS BusinessEntityID.GetLevel(),
    EmployeeName NVARCHAR(50) NOT NULL
);
GO

CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization (OrgLevel, BusinessEntityID);
GO

CREATE UNIQUE INDEX Org_Depth_First
ON Organization (BusinessEntityID);
GO

Examples

本文中的程式代碼範例會使用 AdventureWorks2025 或 AdventureWorksDW2025 範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案首頁下載。

基本範例

下列範例有意經過簡化以協助您開始使用。首先，建立資料表以保留一些地理資料。

CREATE TABLE BasicDemo (
    [Level] HIERARCHYID NOT NULL,
    Location NVARCHAR(30) NOT NULL,
    LocationType NVARCHAR(9) NULL
);

現在，插入一些大陸、國家/地區、州和城市的資料。

INSERT BasicDemo
VALUES ('/1/', 'Europe', 'Continent'),
    ('/2/', 'South America', 'Continent'),
    ('/1/1/', 'France', 'Country'),
    ('/1/1/1/', 'Paris', 'City'),
    ('/1/2/1/', 'Madrid', 'City'),
    ('/1/2/', 'Spain', 'Country'),
    ('/3/', 'Antarctica', 'Continent'),
    ('/2/1/', 'Brazil', 'Country'),
    ('/2/1/1/', 'Brasilia', 'City'),
    ('/2/1/2/', 'Bahia', 'State'),
    ('/2/1/2/1/', 'Salvador', 'City'),
    ('/3/1/', 'McMurdo Station', 'City');

選取資料，並加入資料行以將層級資料轉換為容易了解的文字值。此查詢也會依 hierarchyid 資料類型來排序結果。

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

結果集如下所示。

Converted Level  Level     Location         LocationType
---------------  --------  ---------------  ---------------
/1/              0x58      Europe           Continent
/1/1/            0x5AC0    France           Country
/1/1/1/          0x5AD6    Paris            City
/1/2/            0x5B40    Spain            Country
/1/2/1/          0x5B56    Madrid           City
/2/              0x68      South America    Continent
/2/1/            0x6AC0    Brazil           Country
/2/1/1/          0x6AD6    Brasilia         City
/2/1/2/          0x6ADA    Bahia            State
/2/1/2/1/        0x6ADAB0  Salvador         City
/3/              0x78      Antarctica       Continent
/3/1/            0x7AC0    McMurdo Station  City

即使階層內部不一致，仍具有有效的結構。 Bahia 是唯一的州。它會在階層中顯示為與城市 Brasilia 同層級。同樣地，McMurdo Station 沒有父國家/地區。使用者必須決定此階層類型是否適用。

加入另一個資料列並選取結果。

INSERT BasicDemo
VALUES ('/1/3/1/', 'Kyoto', 'City'),
    ('/1/3/1/', 'London', 'City');

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

這會顯示更多可能的問題。 Kyoto 可插入為層級 /1/3/1/，但卻沒有父層級 /1/3/。而且 London 和 Kyoto 兩者的 hierarchyid 值相同。同樣地，使用者必須決定此階層類型是否適用，並封鎖不適用的值。

此外，此資料表未使用階層 '/' 的頂端。由於所有大陸沒有共同的父系，因此已省略。您可以透過加入整個地球來新增一個。

INSERT BasicDemo
VALUES ('/', 'Earth', 'Planet');

從父/子移轉為 hierarchyid

大多數的樹都是以父／子關係表示。從父/子結構遷移至使用 hierarchyid 的資料表，最簡單的方法是使用暫存資料行或暫存資料表，來追蹤階層中各層級的節點數量。如需遷移 [父子式] 資料表的範例，請參閱教學課程：使用 hierarchyid 資料類型的第 1 課。

使用 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 INTO 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 會使用範圍搜尋。除了應用程式可能想要檢查的其他錯誤情況之外，若在插入後發生重複鍵違規，表示有人嘗試新增多位具有相同 ID 的員工，因此必須重新計算 @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;

    SELECT @last_child = EmployeeId.GetDescendant(LastChild, NULL)
    FROM Org_T2
    WHERE EmployeeId = @mgrid;

    UPDATE Org_T2
    SET LastChild = @last_child
    WHERE EmployeeId = @mgrid;

    INSERT Org_T2 (EmployeeId, EmployeeName)
    VALUES (@last_child, @EmpName);

    COMMIT;
END;

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

INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES (HIERARCHYID::GetRoot(), 'David');
GO

EXECUTE AddEmp 0x, 'Sariya';
GO

EXECUTE AddEmp 0x58, 'Mary';
GO

SELECT * FROM Org_T2

結果集如下所示。

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

強制執行樹狀結構

上述範例說明應用程式可以如何確保樹狀結構的維護。若要使用約束條件來強制實作樹狀結構，可以建立一個計算資料行來定義每個節點的父節點，並以回參照主鍵 ID 的外部索引鍵條件約束加以限制。

CREATE TABLE Org_T3 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    ParentId AS EmployeeId.GetAncestor(1) PERSISTED FOREIGN KEY 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; // SqlFunction Attribute
using Microsoft.SqlServer.Types;  // SqlHierarchyId

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.IsDescendantOf(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 AS 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
INNER 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