TSQL HierarchyID Data Type usage

The HierarchyID data type is a variable-length system data type. You can use HierarchyID to represent locations in a hierarchy. hierarchyid to Represent position in a hierarchy. "Data-guid=" 92abe496f5464189db77fdc60976e369 ">hierarchyid does not automatically represent A tree." Data-gu Id= "Cba58b8db06eb8e9f0915fc00aae0e02" is type HIERARCHYID  The column hierarchyid values in such a by that the desired relationship between rows are reflected in the values. "Data-guid=" 18 bf0e9a171daa797a20ce6e3245b554 "" By the application to generate and assign HIERARCHYID 

hierarchyid to Represent position in a hierarchy. "Data-guid=" 92abe496f5464189db77fdc60976e369 ">hierarchyid does not automatically represent a tree. "Data-guid=" cba58b8db 06eb8e9f0915fc00aae0e02 ">hierarchyid values in such a by that the desired relationship between rows are reflected in the values . "Data-guid=" 18bf0e9a171daa797a20ce6e3245b554 "> 1, string representation

hierarchyid to Represent position in a hierarchy. "Data-guid=" 92abe496f5464189db77fdc60976e369 ">hierarchyid does not automatically represent a tree. "Data-guid=" cba58b8db 06eb8e9f0915fc00aae0e02 ">hierarchyid values in such a by that the desired relationship between rows are reflected in the values . "Data-guid=" 18bf0e9a171daa797a20ce6e3245b554 "> must start with"/", End With"/", use a positive integer between"/"to identify an element, for example" /","/1/2/","/1/2/3/"

2, conversion of data types

Declare @sa Nvarchar( -)Declare @haHierarchyIDDeclare @hbHierarchyIDSet @sa='/1/2/3/'Set @ha=HierarchyID::P Arse (@sa)Set @hb=0x5b5eSelect @sa  asSa@ha  asHa@hb. ToString () asHb

3, compare by depth precedence

Given two HierarchyID values A and b,a<b indicate that when a depth-first traversal is performed on the tree, A is found and B is found. The index of the HierarchyID data type is sorted in depth precedence, and the storage location of adjacent nodes in the depth-first traversal is also adjacent. The node at the same level, the left node is smaller than the right node, indicating that the left is traversed first.

Declare @sa Nvarchar( -)Declare @sb Nvarchar( -)Declare @haHierarchyIDDeclare @hbHierarchyIDSet @sa='/1/2/3/'Set @sb='/1/2/'Set @ha=HierarchyID::P Arse (@sa)Set @hb=HierarchyID::P Arse (@sb)if @ha>=@hbPrint @sa+'>='+@sbElse Print @sa+'<'+@sb

4, call Getlevel () to view the level of HierarchyID, the value is the number of layers starting from the root node.

Declare @sa Nvarchar( -)Declare @haHierarchyIDSet @sa='/1/2/3/'Set @ha=HierarchyID::P Arse (@sa)Select @sa  asSa@ha  asHa@ha. Getlevel () as  Level

5, static method Getroot (), call format for static methods: Hierarchyid::getroot ()

Select  as  as Roothierarchyid

6,getdescendant (childleft,childright) is used to return a child node of the parent, and the child nodes returned are at the same level as children.

Declare @sa Nvarchar( -)Declare @sb Nvarchar( -)Declare @sr Nvarchar( -)Declare @haHierarchyIDDeclare @hbHierarchyIDDeclare @hrHierarchyIDSet @sa='/1/2/3/'Set @sb='/1/2/6/'Set @sr='/1/2/'Set @ha=HierarchyID::P Arse (@sa)Set @hb=HierarchyID::P Arse (@sb)Set @hr=HierarchyID::P Arse (@sr)Select  @hr. Getdescendant (NULL,NULL). ToString (),@hr. Getdescendant (@ha,NULL). ToString (),@hr. Getdescendant (@ha,@hb). ToString ()

7, determine whether the descendant of the node, child. Isdescendantof (parent), if yes, returns 1, if not, returns 0

Declare @sa Nvarchar( -)Declare @sb Nvarchar( -)Declare @sr Nvarchar( -)Declare @haHierarchyIDDeclare @hbHierarchyIDDeclare @hrHierarchyIDSet @sa='/1/2/3/'Set @sb='/1/2/6/'Set @sr='/1/2/'Set @ha=HierarchyID::P Arse (@sa)Set @hb=HierarchyID::P Arse (@sb)Set @hr=HierarchyID::P Arse (@sr)Select    @ha. Isdescendantof (@hr),        @hb. Isdescendantof (@hr),        @ha. Isdescendantof (@hb)

TSQL HierarchyID Data Type usage