sqlserver2008新数据类型

转载

mb5ff2f2ed7d163 2010-09-14 16:10:00

文章标签 sql 子树 microsoft 常用操作应用程序 文章分类 代码人生

尽管 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 ;