Thought of Genius

以往使用關聯式資料庫建立、維護階層式資料（例如檔案架構、組織階層）都是透過子查詢以及自我關聯(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 />