sqlserver2008新数据类型

http://msdn.microsoft.com/zh-cn/library/bb677212.aspx

尽管 hierarchyid 列不一定表示树，但应用程序可以很容易地确保此列表示树。

• 生成新的值时，请执行下列操作之一：
  
  • 跟踪父行中的最后一个子级编号。
    
  • 计算最后一个子级。若要高效地执行此操作，需要使用广度优先索引。
    
• 通过对列创建可能属于聚集键的唯一索引，强制实现唯一性。若要确保插入的值是唯一的，请执行下列操作之一：
  
  • 在可序列化事务中确定每个新子节点的唯一性并将其插入。
    
  • 检测唯一键冲突故障并重试。
    

使用错误检测的示例

在下例中，示例代码计算新子级的 EmployeeId 值，接着检测有无键冲突，然后返回 INS_EMP 标记以便重新计算新行的 EmployeeId 值：

复制

USE AdventureWorks2008R2;
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 是一个范围查找。除了应用程序可能需要检查的其他错误情况之外，插入后出现重复键冲突表示试图添加具有同一 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 

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

返回页首

 

强制表示树

---

以上示例说明了应用程序如何确保树得到维护。若要通过约束强制表示树，创建定义各节点父级的计算列时可以为它创建一个反过来约束主键 ID 的外键。

复制

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 OrgNode.IsDescendant(@nold) = 1 ;

复制

COMMIT TRANSACTION
END ;
GO