Thought of Genius

相信大家看過了 ｢ SQL Server 2008 HierarchyID ｣這篇文章，應該對於 HierarchyID 資料型態有些許的了解。但是當我們每次輸入資料時，都必須撰寫這麼複雜的T-SQL語法，實在很不方便。
所以使用 HierarchyID 通常都會搭配預存程序，這樣程式開發人員就不需要了解整個的架構，使用起來也比較方便。

以下沿用｢ SQL Server 2008 HierarchyID ｣的範例，介紹五個比較常用的預存程序。
<br />-- Create procedure to insert HierarchyEmp record<br />CREATE PROCEDURE usp_AddHierarchyEmp (@leaderid INT, @empid INT, @empname VARCHAR(15), @title VARCHAR(30)) AS<br />BEGIN<br />DECLARE @Parent HIERARCHYID, @LastChild HIERARCHYID<br />SELECT @Parent = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @leaderid<br />SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />BEGIN TRANSACTION<br />SELECT @LastChild = max(OrgNode)FROM HR.HierarchyEmp WHERE OrgNode.GetAncestor(1) = @Parent ;<br />INSERT INTO HR.HierarchyEmp VALUES(@Parent.GetDescendant(@LastChild, NULL), @empid, @empname, @title)<br />COMMIT<br />END ;<br />GO<br />-- Execute procedure to insert new child node<br />EXEC usp_AddHierarchyEmp 11, 101, 'Carlos Oliveira', 'Junior Technician'<br />EXEC usp_AddHierarchyEmp 13, 102, 'Rebecca Chambers', 'Junior Sales'<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Create procedure to find out all Parents<br />CREATE PROCEDURE usp_FindParents (@empid INT) AS<br />BEGIN<br />DECLARE @BossNode HIERARCHYID<br />SELECT @BossNode = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @empid ;<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp WHERE @BossNode.IsDescendantOf(OrgNode) = 1<br />END ;<br />GO<br />-- Execute procedure to find out all Parents<br />EXEC usp_FindParents 12<br /><br /><br /><br />-- Create procedure to find out all children<br />CREATE PROCEDURE usp_Findchildren (@empid INT) AS<br />BEGIN<br />DECLARE @BossNode HIERARCHYID<br />SELECT @BossNode = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @empid ;<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp WHERE OrgNode.IsDescendantOf(@BossNode) = 1<br />END ;<br />GO<br />-- Execute procedure to find out all children<br />EXEC usp_Findchildren 2<br /><br /><br /><br />-- Create procedure to find out menber of level<br />CREATE PROCEDURE usp_FindForLevel (@empid INT) AS<br />BEGIN<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp WHERE OrgNode.GetLevel() = @empid<br />END ;<br />GO<br />-- Execute procedure to find out menber of level<br />EXEC usp_FindForLevel 3<br /><br /><br /><br />-- Create procedure to move OrgNode<br />CREATE PROCEDURE usp_MoveOrgHierarchyEmp(@oldMgr INT, @newMgr INT) AS<br />BEGIN<br />DECLARE @nold HierarchyID<br />DECLARE @nnew HierarchyID<br />SELECT @nold = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @oldMgr ;<br />SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />BEGIN TRANSACTION<br />SELECT @nnew = OrgNode FROM HR.HierarchyEmp WHERE EmpID = @newMgr ;<br />SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) FROM HR.HierarchyEmp WHERE OrgNode.GetAncestor(1)=@nnew ;<br />UPDATE HR.HierarchyEmp SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew) WHERE OrgNode.IsDescendantOf(@nold) = 1 ;<br />COMMIT TRANSACTION<br />END ;<br />GO<br />-- Save data as temporal table<br />SELECT * INTO ##tmp FROM HR.HierarchyEmp<br />-- Execute procedure to move OrgNode<br />EXEC usp_MoveOrgHierarchyEmp 11,13<br />SELECT t.OrgNode.ToString() as OrgNodeText, t.*, h.OrgNode.ToString() as OrgNodeText, h.*<br />FROM HR.HierarchyEmp as h FULL OUTER JOIN ##tmp as t ON t.OrgNode = h.OrgNode<br /><br /><br /><br />-- Clean up test environment<br />DROP PROCEDURE usp_AddHierarchyEmp, usp_MoveOrgHierarchyEmp;<br />DROP PROCEDURE usp_FindParents, usp_Findchildren, usp_FindForLevel;<br />

以往使用關聯式資料庫建立、維護階層式資料（例如檔案架構、組織階層）都是透過子查詢以及自我關聯(self join)的方式呈現各階層的節點值，並於查詢語法內計算該節點屬於第幾階層，以及每個節點的路徑。即使設計是簡單的，但查詢它可不是件容易的事情。例如：1.找出某個僱員的所有下屬，2.算出僱員在公司裡的級別，等等。
SQLServer2000用戶使用基於游標或臨時表的方法來寫這些查詢。雖然簡單，卻犧牲了易維護性和性能。
SQLServer2005提出了一個巧妙的方法：在T-SQL裡使用CTE。CTE允許你寫遞歸查詢。CTE對於用表來呈現樹形結構有了很大的改進，但也留下了一些問題。查詢複雜度降低了，但是性能呢？即使優化了CTE的執行計劃，你也可能處於可能沒有索引的境地。

SQL Server 2008 HierarchyID 資料型態：
由 SQLCLR UDT 實做的特殊資料型態，透過該資料型態可讓儲存與查詢階層式資料更容易。當同一階層內，節點有先後順序的關聯性時，HierarchyID 資料型態最為適合。另外，HierarchyID 資料型態的值會以極度壓縮的方式存於欄位中。

階層式資料索引策略：
深度優先搜尋（Depth-First-Search）：
是沿著樹的深度瀏覽樹的節點，如果發現目標，則演算法中止，屬於盲目搜索。這類演算法可用於：尋找資料夾及其子資料夾中的所有檔案，或是搜尋某位經理所帶領的員工及其下屬有幾位。

廣度優先搜尋( Breadth-First Search )：
會先從根節點開始，沿著橫向的寬度瀏覽各節點，如果發現目標，則演算法中止。廣度優先會將資料列與階層的每個層級儲存在一起。例如，直屬相同經理的員工記錄彼此會儲存在附近。

以下使用一個簡單的範例，來說明 HierarchyID 。
<br />-- Scheme Create schema, table and unique index<br />CREATE SCHEMA HR<br />GO<br />CREATE TABLE HR.HierarchyEmp (<br /> OrgNode HIERARCHYID,<br /> OrgLevel AS OrgNode.GetLevel(),<br /> EmpID INT NOT NULL,<br /> EmpName VARCHAR(15) NOT NULL,<br /> Title VARCHAR(30) NOT NULL<br />);<br />GO<br />CREATE UNIQUE CLUSTERED INDEX ix_HierarchyEmp ON HR.HierarchyEmp (OrgNode,EmpID)<br />GO<br /><br /><br /><br />-- Insert root row<br />INSERT INTO HR.HierarchyEmp VALUES (HIERARCHYID::GetRoot(), 1,'Albert Wesker', 'CEO');<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Insert child Row<br />DECLARE @Manager HIERARCHYID<br />SELECT @Manager = HIERARCHYID::GetRoot() FROM HR.HierarchyEmp;<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(NULL,NULL), 2, 'Chris Redfield', 'CTO')<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />--Insert Third Row<br />DECLARE @Manager HIERARCHYID<br />DECLARE @FirstChild HIERARCHYID<br />SELECT @Manager = HIERARCHYID::GetRoot() FROM HR.HierarchyEmp;<br />SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'Jill Valentine','COO')<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Insert the First Descendant of a Child Node<br />DECLARE @Manager HIERARCHYID<br />SELECT @Manager = CAST('/1/' AS HIERARCHYID)<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(NULL, NULL),11,'Leon Kennedy','Senior Technician');<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />--Insert the Second Descendant of a Child Node<br />DECLARE @Manager HIERARCHYID<br />DECLARE @FirstChild HIERARCHYID<br />SELECT @Manager = CAST('/1/' AS HIERARCHYID)<br />SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(@FirstChild, NULL),12,'Claire Redfield','Senior Technician');<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Insert the first node who is the Descendant of Director Marketing<br />DECLARE @Manager HIERARCHYID<br />DECLARE @FirstChild HIERARCHYID<br />SELECT @Manager = CAST('/2/' AS HIERARCHYID)<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(NULL, NULL),13,'Ada Wang','Senior Sales');<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Insert the second node who is the Descendant of Director Marketing<br />DECLARE @Manager HIERARCHYID<br />DECLARE @FirstChild HIERARCHYID<br />SELECT @Manager = CAST('/2/' AS HIERARCHYID)<br />SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(@FirstChild, NULL),14,'Ashley Graham','Accountant');<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Insert the third node who is the Descendant of Director Marketing<br />-- in between 2 existing descendants<br />DECLARE @Manager HIERARCHYID<br />DECLARE @FirstChild HIERARCHYID<br />DECLARE @SecondChild HIERARCHYID<br />SELECT @Manager = CAST('/1/' AS HIERARCHYID)<br />SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)<br />SELECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)<br />INSERT INTO HR.HierarchyEmp VALUES (@Manager.GetDescendant(@FirstChild, @SecondChild),15,'Jack Krauser','Senior Technician');<br />SELECT OrgNode.ToString() as OrgNodeText, * FROM HR.HierarchyEmp<br /><br /><br /><br />-- Clean up test environment<br />DROP TABLE HR.HierarchyEmp;<br />DROP SCHEMA HR;<br />