[SQL] 大量更新造成統計資訊老舊

該實務情況為公司產線單位有調整，為維持 AP 端運作資料上有進行大量更新，以往產線是依靠 [工單檔頭 Table] 的 [未完工 Index] 先跑篩選資料再進行後續，但資料大量更新後統計資訊不準確且剛好 pass 掉下述三種統計資訊維護情況

• 資料大量更新後沒有手動更新
• 未達到自動更新門檻
• 每月排程更新時程也還沒到

所以最近就常出現 blocking 和 deadlock 情況

統計資訊不準確執行計畫

可以看出從 [檔身 Table] 抓出 81,327 筆資料，然後跑 Nested Loop 進 [檔頭 Table] 內，最後只抓出 414 筆資料而且 [Index Seek Operator] 內的 Actual Row (81,327) 和 Estimated Row (1) 差異過大

統計資訊更新後執行計畫

從 [檔頭 Table] 開始跑，只抓出 494 筆資料出來運作而已

Logical Read 比較

懶得去計算改善多少倍了，XD

Table	統計資訊不精確	更新統計資訊後
檔頭	243,819	9
檔身	326,184	2,551

• 延伸閱讀：
• [SQL] Nested Loops Join

[C#] UserControl 自訂事件

建立 UserControl 時，內部通常都會有多個子控件，當要註冊 UserControl.MouseClick 使用時會發生問題，問題在於子控件會蓋在 UserControl 上，所以當滑鼠點擊時，其實觸發的是 [子控件.MouseClick]，不會是 UserControl.MouseClick

以上圖來說明，UserControl 內放置一個 Label 子控件，當滑鼠點擊觸發事件

• 綠色區域：UserControl.MouseClick
• 藍色區域：Label.MouseClick

該筆記是透過自訂 MouseClick 事件來達到點擊綠色、藍色區域都會觸發

UserControl 內自訂 CustomMouseClick 事件來整合要觸發的相關控件事件

using System.Windows.Forms;

namespace UCMouseClick
{
    public partial class UserControl1 : UserControl
    {
        public event MouseEventHandler CustomMouseClick
        {
            add
            {
                MouseClick += value;
                label1.MouseClick += value;
            }
            remove
            {
                MouseClick -= value;
                label1.MouseClick -= value;
            }
        }

        public UserControl1()
        {
            InitializeComponent();
        }
    }
}

Form 內註冊 UserControl.CustomeMouseClick 來觸發

using System.Windows.Forms;

namespace UCMouseClick
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            userControl11.CustomMouseClick += UserControl11_CustomMouseClick;
        }

        private void UserControl11_CustomMouseClick(object sender, MouseEventArgs e)
        {
            MessageBox.Show("CustomMouseClick");
        }
    }
}

• 延伸閱讀
• [C#] Event 練習
• [C#] 自訂控制項內的事件 1、2

[SQL] 隱含轉換與 Index Seek - 多欄位

在這兩篇筆記

• [SQL] 探查剩餘 (Probe Residual)
• [SQL] 隱含轉換與 Index Seek

有紀錄到隱含轉換發生還是會跑 Index Seek 情況，沒想到遇上多欄位情況，也是從 char 隱含轉換至 nchar，但該隱含轉換欄位並非是多欄位索引第一欄位，仍然能跑 Index Seek

以下為模擬實務情況範例，tblNChar 為供應商系統、tblChar 為我方系統，該欄位資料對於我方來說只會是英數，但從供應商角度，為了配合不同客戶需求，開成 nchar、nvarchar 都是合理的

USE [AdventureWorks2019]
GO

DROP TABLE IF EXISTS tblChar
DROP TABLE IF EXISTS tblNChar

-- 建立 tblChar Table
CREATE TABLE [dbo].[tblChar]
(
	ColDateTime datetime not null ,
	ColChar char(10) not null ,
	CONSTRAINT [PK_tblChar] PRIMARY KEY CLUSTERED 
	(
		ColDateTime ASC , 
		ColChar ASC
	)
)
GO

-- 建立 tblNChar Table
CREATE TABLE [dbo].[tblNChar]
(
	ColDateTime datetime not null ,
	ColNChar nchar(50) not null ,
	CONSTRAINT [PK_tblNChar] PRIMARY KEY CLUSTERED 
	(
		[ColDateTime] ASC , 
		[ColNChar] ASC
	)
) 
GO

-- 模擬實務情況語法
SELECT NC.*
FROM tblNChar AS NC
WHERE NOT EXISTS
	(
			SELECT 1
			FROM tblChar AS C
			WHERE NC.ColDateTime = C.ColDateTime
				AND NC.ColNChar = C.ColChar
	)
	AND NC.ColDateTime >= '20230718'
	AND NC.ColDateTime < '20230719'

執行計畫截圖內

• 搜尋述詞 (Seek Predicates)：ColDateTime (多欄位索引第一欄位)
• 述詞 (Predicates)： ColNChar 和 ColChar (多欄位索引第二欄位)

[SQL] Foreign Key - 多欄位

Foreign Key 關聯欄位是允許 null，但在多欄位情況下會更凸顯欄位允許 null 的問題

官方文章 -  Create Foreign Key Relationships 內容有提到該點

When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column. Otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

已經說明多欄位情況下，FK 要有限制資料唯一性功能，要每個欄位都有值喔

簡易範例來記錄

USE [AdventureWorks2019]
GO

---------- 建立 Table
-- 建立 tblMaster Table 並以 Col1 和 Col2 為 PK

DROP TABLE IF EXISTS tblDetail
DROP TABLE IF EXISTS tblMaster

CREATE TABLE [dbo].[tblMaster](
	[Col1] [char](5) NOT NULL,
	[Col2] [char](10) NOT NULL,
 CONSTRAINT [PK_tblMaster] PRIMARY KEY CLUSTERED 
(
	[Col1] ASC,
	[Col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 建立 tblDetail Table 並建立 Col1 和 Col2 FK

CREATE TABLE [dbo].[tblDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Col1] [char](5) NULL,  -- 允許 null
	[Col2] [char](10) NULL, -- 允許 null
 CONSTRAINT [PK_tblDetail] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblDetail_tblDetail] FOREIGN KEY([Col1], [Col2])
REFERENCES [dbo].[tblMaster] ([Col1], [Col2])
GO

ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tblDetail]

---------- insert 資料並觀察 FK 是否有作用

-- tblMaster
INSERT INTO tblMaster (Col1 , Col2) VALUES('12345' , '1234567890')

-- tblDetail
INSERT INTO tblDetail (Col1 , Col2) VALUES(NULL , NULL)
INSERT INTO tblDetail (Col1 , Col2) VALUES('12345' , NULL)
INSERT INTO tblDetail (Col1 , Col2) VALUES(NULL , '1234567890')

-- 下述兩個 insert 插入不存在 tblmaster 資料 
INSERT INTO tblDetail (Col1 , Col2) VALUES('00000' , NULL)
INSERT INTO tblDetail (Col1 , Col2) VALUES(NULL , '0000000000')

-- Col1 和 Col2 都有值，會引發 FK
INSERT INTO tblDetail (Col1 , Col2) VALUES('00000' , '0000000000')

• 參考資料
• Constraints在資料含有NULL時的隱憂

[SQL] HierarchyID - 函數

在 HierarchyID 三篇筆記內都有介紹、使用相關函數，該篇針對還有心得的函數來筆記

Parse

在 [SQL] HierarchyId - 字串路徑 筆記內是透過 CTE 來產生字串路徑，想說不知道 HierarchyID 是不是可以用字串來組成，結果當然是不行囉，另外在

• Parse 文件內備註提到，不能有尾端空白
• hierarchyid data type method reference 內提到節點不能有前置零

SELECT HierarchyID::Parse('/公司/總經理/人事/')
SELECT HierarchyID::Parse('/1 /')
SELECT HierarchyID::Parse('/1/1.01/')

上述都會拋出該 Exception，參數字串會是輸入字串，以 [ /公司/總經理/人事/] 為例說明

訊息 6522，層級 16，狀態 2，行 5 

執行使用者自訂常式或彙總 "hierarchyid" 時，發生 .NET Framework 錯誤:

Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse 失敗，因為輸入字串 '/公司/總經理/人事/' 不是 SqlHierarchyId 節點的有效字串表示。 Microsoft.SqlServer.Types.HierarchyIdException: 於 Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)

要避免節點發生異常，最保險方式就是使用 GetDescendant() 來產生

GetAncestor()

傳回該子節點的上階節點，n 代表往上 N 個節點，使用語法為

child.GetAncestor(n)

以 [SQL] HierarchyID - 批次 的 tblHID 為範例資料

取得上一階 (父節點，N = 1)

-- '/3/2/1/' 為 DepID = 7 (產線 1-1)
DECLARE @child HierarchyID = HierarchyID::Parse('/3/2/1/') 

-- '/3/2/' 為 DepID = 6 (工作站 1)
SELECT * , HID.ToString()
FROM tblHID
WHERE HID = @child.GetAncestor(1)

取得上兩階 (爺節點，N = 2)

-- '/3/2/1/' 為 DepID = 7 (產線 1-1)
DECLARE @child HierarchyID = HierarchyID::Parse('/3/2/1/') 

-- '/3/' 為 DepID = 4 (廠務 1)
SELECT * , HID.ToString()
FROM tblHID
WHERE HID = @child.GetAncestor(2)

取得上一階全部節點

GetAncestor(1) 為抓取父節點，但一開始理解成抓上一階全部節點，既然都誤會了就來寫看看囉

-- '/3/2/1/' 為 DepID = 7 (產線 1-1)
DECLARE @child HierarchyID = HierarchyID::Parse('/3/2/1/') 

SELECT * , HID.ToString()
FROM tblHID
WHERE HID.IsDescendantOf(@child.GetAncestor(2)) = 1
	AND HID.GetLevel() = @child.GetLevel() -1 

用該張圖來呈現上述三個範例結果

[SQL] TSQL 語法邏輯順序和實際執行

[SQL] T-SQL 語法邏輯順序 常常會被拿來討論資料在何時會被篩選掉，以為內層就會先篩選資料，但 SQL Server Query Optimizer 是把全部 TSQL 一起解析，找出最低執行計畫成本來 run，實際執行流程必須透過執行計畫來得知，邏輯順序只會影響最後結果產出而已

以 AdventureWorks2019.Person.Person 單一 Table 來記錄

範例一：多層

下述語法，由內而外邏輯上會

1. 篩選 BusinessEntityID 1 到 10 資料
2. 篩選 BusinessEntityID 1 到 5 資料
3. 篩選 BusinessEntityID 1 資料

SELECT T2.*
FROM
	(
		SELECT T1.*
		FROM
			(
				SELECT *
				FROM Person.Person
				WHERE BusinessEntityID BETWEEN 1 AND 10
			) AS T1
		WHERE T1.BusinessEntityID BETWEEN 1 AND 5
	) AS T2
WHERE T2.BusinessEntityID = 1

觀察執行計畫發現，直接篩選 BusinessEntityID 1 資料

範例二：內部先執行

下述語法，由內而外邏輯上會

1. Person.Person 和 Person.Person 進行 UNION ALL 形成 T2
2. T1 和 T2 進行 JOIN 並篩選 BusinessEntityID 1 資料

SELECT T1.* 
FROM Person.Person AS T1
	JOIN
		(
			SELECT * FROM Person.Person
			UNION ALL
			SELECT * FROM Person.Person
		) AS T2 ON T1.BusinessEntityID = T2.BusinessEntityID
WHERE T1.BusinessEntityID = 1

觀察執行計畫發現，先篩選 T1 BusinessEntityID 1 資料後，再跑 Nested Loops 進去 T2 篩選資料，篩選完才 UNION ALL，三個 Person.Person Table 都只抓出 1 筆資料而已

[SQL] HierarchyID - 批次

在 [SQL] HierarchyID - CRUD 內有介紹 insert、update、delete，但都是根據單一節點進行處理，該筆記紀錄多節點處理

環境建置

使用 Table 說明

• tblCompany Table Schema 為紀錄上一層 (父子) 架構
• tblHID Table Schema 為 HierarchyID 架構，以最單純方式來建立，直接以 HierarchyID 當成 PK，方便記錄 CRUD

DROP TABLE IF EXISTS tblCompany

CREATE TABLE tblCompany
(
	DepID int PRIMARY KEY ,
	DepName nvarchar(20) ,
	ParentDepID int
)

INSERT INTO tblCompany VALUES(1 , N'公司名稱' , NULL)
INSERT INTO tblCompany VALUES(2 , N'業務' , 1)
INSERT INTO tblCompany VALUES(3 , N'財務' , 1)
INSERT INTO tblCompany VALUES(4 , N'廠務 1' , 1)
INSERT INTO tblCompany VALUES(5 , N'倉庫 1' , 4)
INSERT INTO tblCompany VALUES(6 , N'工作站 1' , 4)
INSERT INTO tblCompany VALUES(7 , N'產線 1-1' , 6)
INSERT INTO tblCompany VALUES(8 , N'產線 1-2' , 6)
INSERT INTO tblCompany VALUES(9 , N'廠務 2' , 1)
INSERT INTO tblCompany VALUES(10 , N'工作站 2' , 9)
INSERT INTO tblCompany VALUES(11 , N'產線 2-1' , 10)
INSERT INTO tblCompany VALUES(12 , N'產線 2-2' , 10)
INSERT INTO tblCompany VALUES(13 , N'人事' , 1)

DROP TABLE IF EXISTS tblHID

CREATE TABLE tblHID  
( 
	HID HierarchyID PRIMARY KEY CLUSTERED ,  
	DepID int UNIQUE NOT NULL,  
	DepName nvarchar(20) NOT NULL
) ;  

文章內容統一以該語法來進行查詢操作後結果，就不在反覆寫出

SELECT 
	HID , 
	HID.ToString() AS HPath ,   
	HID.GetLevel() AS HLevel ,
	DepID, 
	CAST(REPLICATE(SPACE(1) , HID.GetLevel() * 10) + DepName as nvarchar(4000)) AS NameTree
FROM tblHID ;

[SQL] Compute Scalar 延遲特性

延續 [SQL] 傳遞到 LEFT 或 SUBSTRING 函數的參數長度無效，該筆記要記錄執行計畫 operator - Compute Scalar，在上述筆記中有提到 T1 樹狀 Table 單獨執行時不會拋出 Exception，但從執行計畫中可以肯定根結點 A0 資料有被拉出來

T1 TSQL 概念明細語法

SELECT * ,
    SUBSTRING(D.TreePath , 1 , LEN(D.TreePath)-3) AS TreePath
FROM 樹狀檔頭Table AS M
    JOIN 樹狀Table AS D ON M.派工單號 = D.派工單號
WHERE M.是否結案 = 0
    AND D.工作站 = 特定工作站

透過 TSQL count() 統計樹狀 Table 內根結點 A0 資料狀態

• 包含根結點 A0 資料，總共 81,201 筆資料
• 根結點 A0 總共有 391 筆

觀察執行計畫

執行計畫觀察是 81,201 筆資料 (包含根結點 A0 資料) 從樹狀 Table 抓出，遇上 Compute Scalar 卻沒有拋出 Exception，透過 Nested Loosps 跟樹狀檔頭 Table 抓資料時，是透過 [派工單號] 和 [是否結案] 兩個欄位去篩選資料，資料量在此時縮減為 455 筆，也把根結點 A0 資料篩選掉，猜測是 Compute Scalar  並沒有實際執行轉換

查詢 Compute Scalar 相關資料，在這兩篇文章中獲得解答

• Compute Scalar
• Compute Scalars, Expressions and Execution Plan Performance

發現 Compute Scalar 具有延遲特性，不是有該 operator 出現就會立即執行，所以就避開 A0 轉換拋出 Exception

• 參考資料
• Showplan Logical and Physical Operators Reference
• Why does this execution plan have Compute Scalars?
• SQL SERVER – Computed Column and Compute Scalar Operators

[SQL] 傳遞到 LEFT 或 SUBSTRING 函數的參數長度無效

使用者告知說有段 TSQL 語法分開執行都 OK，但是 JOIN 在一起會拋出錯誤

傳遞到 LEFT 或 SUBSTRING 函數的參數長度無效

該 Table 是跑樹狀結構資料，根結點為 A0，節點是以 A0_A1_A2 型式來記錄，會透過 SUBSTRING 來取得父節點，語法如下

DECLARE @TreePath varchar(100) = 'A0_A1_A2'
SELECT SUBSTRING(@TreePAth , 1 , LEN(@TreePath)-3) -- 取得 A0_A1

該問題就很明顯，以 A0 節點去進行 substring 時，長度減 3 就一定會拋出錯誤

實際 TSQL 概念語法

SELECT *
FROM
	(
		SELECT *
			SUBSTRING(TreePath , 1 , LEN(TreePath)-3) AS TreePath
		FROM 樹狀Table
		WHERE Condition1
	) AS T1
	JOIN
	(
		SELECT *
			SUBSTRING(TreePath , 1 , LEN(TreePath)-3) AS TreePath
		FROM 樹狀Table
		WHERE Condition2
	) AS T2 ON T1.TreePath = T2.TreePath

觀察估計執行計畫

從執行計畫中觀察，可以發現 T1 Table 先跑，透過 Compute Scalar (substring 抓父節點) 跑 Nested Loops 去篩選 T2 Table 資料，所以才會拋出錯誤

[SQL] HierarchyID - CRUD

接續 [SQL] HierarchyID - 資料型態，該篇以 HierarchyID CRUD 為主要內容並記錄相關函數，以官方文章 - 在階層式資料表中建立與管理資料 為主要參考

環境建置

Table Schema 以最單純方式來建立，直接以 HierarchyID 當成 PK，方便記錄 CRUD

CREATE TABLE tblHID  
( 
	HID HierarchyID PRIMARY KEY CLUSTERED NOT NULL ,  
	EmpID int UNIQUE NOT NULL ,  
	EmpName varchar(20) NOT NULL ,  
	Title nvarchar(20) NULL 
) ;  

文章內容統一以該語法來進行查詢操作後結果，就不在反覆寫出

SELECT 
	HID , 
	HID.ToString() AS HPath ,   
	HID.GetLevel() AS HLevel ,
	EmpID, 
	CAST(REPLICATE(SPACE(1) , HID.GetLevel() * 10) + EmpName as varchar(4000)) AS NameTree ,
	Title   
FROM tblHID ;

insert

GetRoot()：透過 hierarchyid::GetRoot() 來取得根節點

INSERT tblHID (HID, EmpID, EmpName, Title)  
	VALUES (hierarchyid::GetRoot(), 1, 'David', N'行銷經理') ;  

GetDescendant()：傳回父系子節點，使用語法為

parent.GetDescendant ( child1 , child2 )

語法中 parent、child1、child2 三參數使用重點

• 如果 parent 為 NULL，則會傳回 NULL。
• 如果 parent 不是 NULL，而 child1 和 child2 都是 NULL，則會傳回 parent 的一個子節點。
• 如果 parent 和 child1 都不是 NULL，而 child2 是 NULL，則會傳回 parent 中大於 child1 的子節點。
• 如果 parent 和 child2 不是 NULL，而 child1 是 NULL，則會傳回 parent 中小於 child2 的子節點。
• 如果 parent、child1 和 child2 都不是 NULL，則會傳回 parent 中大於 child1 且小於 child2 的子節點。
• 如果 child1 不是 NULL，也不是 parent 的子節點，則會引發例外狀況。
• 如果 child2 不是 NULL，也不是 parent 的子節點，則會引發例外狀況。
• 如果 child1 >= child2，則會引發例外狀況。

DECLARE @Manager hierarchyid = hierarchyid::GetRoot()  
INSERT tblHID (HID, EmpID, EmpName, Title)  
	VALUES (@Manager.GetDescendant(NULL, NULL), 2, 'Sariya', N'行銷專員') ;

上述語法只能執行一次，當第二次執行時，會拋出違反 PK Exception，因為 GetDescendant 相同參數 (parent、child1、child2) 會得到相同結果

為方便後續建立子節點資料，建立該 Store Procedure 來反覆 insert 資料

CREATE OR ALTER PROCEDURE AddEmp
(
	@mgrID int null, 
	@empID int, 
	@empName varchar(20), 
	@title nvarchar(20))   
AS   
BEGIN  

	DECLARE @mgrHID hierarchyid, @lastChildHID hierarchyid , @hID hierarchyid

	IF @mgrID IS NULL
		SET @hID = HIERARCHYID::GetRoot();
	ELSE
		BEGIN

			SET @mgrHID = (SELECT HID FROM tblHID WHERE EmpID = @mgrID);

			-- 取得該父節點下最大子節點
			SET @lastChildHID =
			(
				SELECT MAX(HID)   
				FROM tblHID   
				-- HID.GetAncestor(1)：該節點父節點
				WHERE HID.GetAncestor(1) = @mgrHID
			);

			SET @hID = @mgrHID.GetDescendant(@lastChildHID, NULL);
		END
	
	INSERT tblHID (HID, EmpID, EmpName, Title)  
		VALUES(@hID, @empID, @empName, @title)
END ;  
GO

EXEC AddEmp 1, 3, 'John', N'行銷專員' ;  
EXEC AddEmp 1, 4, 'Jill', N'行銷專員' ;  
EXEC AddEmp 2, 5, 'Wanida', N'行銷助理' ;  
EXEC AddEmp 3, 6, 'Mary', N'行銷助理' ;
GO

insert 後結果

update 

GetReparentedValue：該 node 從 oldParent 移至 newParent 的 HierarchyID

使用語法為

node.GetReparentedValue(oldParent, newParent)

實際執行

DECLARE @node hierarchyid , @oldParent hierarchyid, @newParent hierarchyid 
-- Wanida：待移動節點
SELECT @node = HID FROM tblHID WHERE EmpID = 5 ; 
-- Sariya：舊父節點
SELECT @oldParent = HID FROM tblHID WHERE EmpID = 2 ;
-- Jill：新父節點
SELECT @newParent = HID FROM tblHID WHERE EmpID = 4 ; 

UPDATE tblHID
SET HID = @node.GetReparentedValue(@oldParent, @newParent)   
WHERE HID = @node ;  
GO

delete 

刪除節點跟一般 TSQL delete 語法無異，但要特別注意刪除節點時，該節點是否還有子節點，萬一還有子節點情況下，子節點會變成孤兒節點

實際執行 

DELETE FROM tblHID WHERE EmpID = 4

• 參考資料
• 階層式資料
• HierarchyID Data Type in SQL Server 2008
• GetDescendant()、GetAncestor()、GetReparentedValue()
• 架構面試題 #3, RDBMS 處理樹狀結構的技巧

[SQL] HierarchyID - 資料型態

SQL Server 2008 推出 HierarchyID 資料類型，主要是用來呈現 [樹狀圖 (階層式)] 資料 ，例如組織圖、BOM 表，以往 Table 都是以 [紀錄上一層 (父子關係)] 來記錄，其缺點是搜尋子節點須以 CTE  遞迴擴展子節點，HierarchyID 則是能改善該情況

紀錄上一層

下圖示意圖

• 左方 Table Schema 透過 ParentEmpID 來記錄上一層資料
• 右方為透過 CTE 遞迴來呈現整個階層關係        

紀錄上一層非該筆記重點，相關應用請參考筆記

• [Challenge] 組織圖
• [Challenge] 計算各經理和其部屬薪資
• [Challenge] 從字串中產生關係樹狀結構
• [Challenge] 找出從屬關係

HierarchyID

HierarchyID 需以斜線符號 (/) 開頭 (樹狀圖根結點 root)，也必須以斜線符號 (/)  結尾，中間使用數字 (int、decimal) 標示各節點，例如：/1/2.1/3，以 binary 來儲存

下述 Table Script 包含 HierarchyID 欄位和相關應用

CREATE TABLE tblHID  
( 
	ID int PRIMARY KEY CLUSTERED ,
	HID HierarchyID UNIQUE NOT NULL ,
	ParentHID AS HId.GetAncestor(1) PERSISTED REFERENCES tblHID (HID),  
	HLevel AS HID.GetLevel() PERSISTED,   
	EmpID int UNIQUE NOT NULL,  
	EmpName varchar(20) NOT NULL,  
	Title nvarchar(20) NULL 
) ;  

Primary Key

Primary Key 預設是 Clustered Index，HierarchyID 是 binary 不適合當成 Clustered Index， 所以建立 ID 流水號欄位來當成 Primary Key (Clustered Index)，官方文件範例上介紹 HierarchyID 時都會把它設定為 Primary Key，覺得這是演示方便才這樣直接設定

HierarchyID 本身沒有限定資料是唯一，該點則是透過 unique 來達到、避免重覆，另外 ID 流水號欄位為方便 AP 端事先取得編號來繪製 UI 上的樹狀圖節點，並不會使用 identity，個人是使用 Sequence 來取代 identity，詳見該篇筆記 - [SQL] Sequence

Foreign Key 

在 [紀錄上一層] 內紀錄父節點，可以設定 FK 來維持資料一致性，避免孤兒資料產生，該作法也可以應用在 HierarchyID 上，該 FK 觀念可以參考該篇筆記 - [SQL] 自我參考 Foreign Key

該 Table 則是使用計算欄位搭配 HierarchyID.GetAncestor(1) 來取得父節點並設定 FK 來達到需求，但該點不是一定要存在，端看實務上是否有該使用 FK 資料維護需求囉

樹狀圖 Level 欄位

該欄位可以直接使用 HierarchyID.GetLevel 來取得，該欄位關係廣度優先索引建立，也是不一定要存在，參考官方文章 - HierarchyID 的索引策略

HierarchyID 簡易顯示

HierarchyID 為 binary，透過 ToString() 或是 CAST() 可以轉成路徑，較方便閱讀

SELECT
	HID ,
	HID.ToString() AS HIDPath ,
	ParentHID ,
	CAST(ParentHID AS nvarchar(4000)) AS ParentHIDPath,
	HLevel ,
	EmpID ,
	CAST(REPLICATE(SPACE(1) , HLevel * 10) + EmpName as varchar(4000)) AS NameTree
FROM tblHID
ORDER BY HIDPath

• 參考資料
• Hierarchy ID - Model Your Data Hierarchies With SQL Server 2008
• 階層式資料
• ToString、GetAncestor、GetLevel