---------内置函数------------
select hierarchyid::GetRoot()--0x
select hierarchyid::Parse('/1/1/') --0x5AC0
select cast(0x5AC0 as hierarchyid)--0x5AC0
select cast('/1/' as hierarchyid)--0x5AC0
select cast(0x5AC0 as hierarchyid).ToString()--/1/1/
select cast(0x5AC0 as hierarchyid).GetLevel()--2
-----------IsDescendantOf------------ 判断@Node是否为@parent的子节点
declare @Node hierarchyid
declare @parent hierarchyid
set @Node='/1/2/3/4/'
set @parent='/1/2/'
select @Node.IsDescendantOf(@parent)--1
select @parent.IsDescendantOf(@Node)--0
--------GetAncestor ( n ) ----------返回指定层次的祖先.
declare @hy hierarchyid
declare @c int
set @hy='/1/1/2/1/'
set @c=@hy.GetLevel()
select @hy.GetAncestor(0).ToString()--/1/1/2/1/
select @hy.GetAncestor(1).ToString()--/1/1/2/
select @hy.GetAncestor(@c).ToString()--/
select @hy.GetAncestor(@c+1).ToString()--null
-----------GetDescendant-----------返回子集
//1.如果父级为NULL，则返回NULL。
---如果父级不为NULL----
--2.而child1 和child2 为NULL，则返回父级的子级。--
declare @hy hierarchyid
set @hy='/1/1/'
select @hy.GetDescendant(NULL,NULL).ToString()--/1/1/1/
--3. 返回值 在child1于child2之间 , child1>child2 且必须为@hy的子集--
select @hy.GetDescendant('/1/1/5/',null).ToString()--/1/1/6/
select @hy.GetDescendant(null,'/1/1/5/').ToString()--/1/1/4/
select @hy.GetDescendant('/1/1/2/','/1/1/5/').ToString()--/1/1/3/
select @hy.GetDescendant('/1/1/3/','/1/1/4/').ToString()--/1/1/3.1/
select @hy.GetDescendant(null,'/1/1/1/5/').ToString()--报异常
select @hy.GetDescendant('/1/1/5/','/1/1/3/').ToString()--报异常
---------◆GetReparentedValue ：可以用来移动节点 --------------
注意：@parent是Node的祖先
declare @Node hierarchyid
DECLARE @NodeChild1 hierarchyid
declare @parent hierarchyid, @new hierarchyid
set @Node='/1/2/3/4/'
set @NodeChild1='/1/2/3/4/5/'
set @parent='/1/2/'
set @new='/5/6/7/'
SET @Node=@Node.GetReparentedValue(@parent, @new)
select @Node.ToString()--/5/6/7/3/4/
SELECT @NodeChild1.GetReparentedValue(hierarchyid::Parse('/1/2/3/4/'), @Node).ToString()
另一项常用操作是移动子树。下面的过程采用 @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.IsDescendantOf(@nold) = 1 ;
COMMIT TRANSACTION
END ;
GO
参考：
http://technet.microsoft.com/zh-cn/library/bb677212(v=sql.105).aspx
http://blog.csdn.net/tjvictor/article/details/4395681
http://msdn.microsoft.com/zh-cn/library/bb677290(v=sql.105).aspx
http://entityframework.codeplex.com/discussions/415185
http://stackoverflow.com/questions/25195890/need-examples-for-entityframework-hierarchyid
http://blog.csdn.net/tonyzhou2008/article/details/5100683
http://www.tuicool.com/articles/iEnum2f
http://blog.csdn.net/szstephenzhou/article/details/8277667
http://blog.csdn.net/ldslove/article/details/5628007