在數據查詢中，從2008開始SQL Server提供了一個新的數據類型hierarchyid，專門用來操作層次型數據結構。
hierarchyid? 類型對層次結構樹中有關單個節點的信息進行邏輯編碼的方法是：對從樹的根目錄到該節點的路徑進行編碼。?
這種路徑在邏輯上表示為一個在根之后被訪問的所有子級的節點標簽序列。 表示形式以一條斜杠開頭，只訪問根的路徑由單條斜杠表示。 對于根以下的各級，各標簽編碼為由點分隔的整數序列。 子級之間的比較就是按字典順序比較由點分隔的整數序列。 每個級別后面緊跟著一個斜杠。 因此斜杠將父級與其子級分隔開。 例如，以下是長度分別為 1 級、2 級、2 級、3 級和 3 級的有效 hierarchyid 路徑：
? /?
? /1/?
? /0.3.-7/?
? /1/3/?
? /0.1/0.2/
在沒有hierarchyid的日子里，我們通過CTE的方式來查詢父以及全部的下級，但是，數據量多的情況下，CTE的方式將會變的很慢，后來，我們通過構造PATH的方式來加快速度。那么，有了hierarchyid類型后，自然得使用hierarchyid了。
現在，通過一個實際的例子來看看hierarchyid的威力。
?
一：CTE方式
WITH?CTEGetChild?AS?? (?? ????SELECT?*?FROM?EL_Organization.Organization?WHERE?ID='ecc43c7159924dca91e2916368f923f4'?--and?[State]=0?and?AuditState=2 ????UNION?ALL?? ?????( ????????SELECT?A.*?FROM?EL_Organization.Organization?AS?A ????????INNER?JOIN?CTEGetChild?AS?B?ON?a.PARENTID=B.ID??--and?A.[State]=0?and?A.AuditState=2 ?????)?? )
查詢出來4489行，需要25S。
看來CTE方式已經到了不能容忍的地步，那么，現在，我們就用它來進行優化。
二：hierarchyid
首先，我們得新建該字段，然后為其賦值，
create?function?f_cidname(@id?varchar(50))?returns?varchar(max)?as? begin? declare?@pids?nvarchar(max);? declare?@pNames?nvarchar(max);? set?@pids='';? set?@pNames='';? with?cte?as? (?select?id,parentid,name?from?EL_Organization.Organization?where?id?=@id--'00037fdf184e48d084b87c3499e3c0e5' union?all? select?b.id,b.parentid,b.name?from?cte?A?,EL_Organization.Organization?B?where?a.parentid?=?b.id? ) select?@pids=convert(varchar(32),Convert(int,?Convert(varbinary(max),?id)))??+?'/'+?@pids?from?cte? return?[email?protected] end? go
接著，我們需要Update全表：
UPDATE?EL_Organization.Organization?SET?PIDS=dbo.f_cidname(id)
注意，id是guid的32位字符串，而hierarchyid字段不支持那么大的Path內路徑，于是我們將GUID轉為了整型：convert(varchar(32),Convert(int, Convert(varbinary(max), id)))?
2.1 TIP
Exception?message:?DataReader.GetFieldType(4)?returned?null.?Exception?data:?System.Collections.ListDictionaryInternal
注意，極有可能我們把字段更新上去后，我們的程序卻出錯了，如上。這個時候，我們需要把
C:Program?FilesMicrosoft?SQL?Server100SDKAssembliesMicrosoft.SqlServer.Types.dll
這個DLL打包到我們的應用程序中去。原因不解釋了。
看看效果吧，修改過后的代碼為：
DECLARE?@tmpIds?hierarchyid SELECT?@tmpIds=Pids?FROM?EL_Organization.Organization?WHERE?ID='ecc43c7159924dca91e2916368f923f4'; WITH?CTEGetChild?AS??( ????SELECT?*?FROM?EL_Organization.Organization?WHERE?ID='ecc43c7159924dca91e2916368f923f4' ????UNION?ALL( ????SELECT?*?FROM?EL_Organization.Organization?WHERE?Pids.IsDescendantOf(@tmpIds)=1? ????) ) SELECT?*?FROM?CTEGetChild
現在，我們的時間到了1S內。
2.2 一切為了不動應用層代碼
現在，既然，增加了一個字段，我們就要維護這個字段，如：本條記錄在應用程序中被移動到了別的父級下，就需要更新這個字段。為了不動上層代碼，唯一能做的就是創建觸發器，即：原有的ParentId變動的時候，就需要更新這個PIds字段，于是，我們創建觸發器如下：
create?trigger?UpdateOrgPIds on?EL_Organization.Organization after?update as if?update?([ParentId]) begin ?????declare?@tmpId?varchar(36) ?????select?@tmpId=id?from?inserted? ?????update?EL_Organization.Organization?set?pids=dbo.f_cidname(@tmpId) end? go --?drop??trigger?EL_Organization.UpdateOrgPIds
