SQL Server uses Hierarchyid to manipulate hierarchical data

Posted by OnePlus on Fri, 05 Jul 2019 20:24:24 +0200

Hierarchical data is defined as a set of data items that are correlated through hierarchical relationships. In hierarchical relationships, a data item is the parent or child of another item.

Starting in 2008, SQL Server built-in hierarchyid data types make it easier to store and query hierarchical data. Hierarchy is actually a CLR data type.

If you don't know what you are talking about, you can actually do it. Then you can go back and understand it.

Next, create a table and insert some data:

create table RoleMan
(
    NodeId    hierarchyid not null,
    RoleId    int not null,
    RoleName    nvarchar(32) not null,
    Par_NodeId    as NodeId.GetLevel()    -- GetLevel()Used to determine the depth (level) of the current level, the top level (root node) is 0, and then add 1 in turn.
)
go

insert into RoleMan(NodeId,RoleId,RoleName)
select '/1/','1','Super Administrator' union 
select '/1/1/','2','Administrators A' union 
select '/1/2/','3','Administrators B' union 
select '/1/1/1/','4','user AA' union 
select '/1/1/2/','5','user AB' union 
select '/1/2/1/','6','user BA' union 
select '/1/2/2/','7','user BB' 
go

select *,
NodeId.ToString() NodeId_Path    -- because hierarchyid The value of a type is expressed in hexadecimal, which is converted to a string.
from RoleMan

Query the ancestor node of the specified node:

-- Query the ancestor node of the specified node
declare @NodeId    hierarchyid

select @NodeId=NodeId 
from RoleMan 
where RoleId=5

select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where @NodeId.IsDescendantOf(NodeId)=1    -- IsDescendantOf(NodeId)，Determines whether the specified node is a descendant of another node, and if so, returns 1

Query the descendant node of the specified node:

-- Query descendant nodes of specified nodes
declare @NodeId    hierarchyid

select @NodeId=NodeId
from RoleMan 
where RoleId=2

select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where NodeId.IsDescendantOf(@NodeId)=1    -- IsDescendantOf(NodeId)，Determines whether the specified node is a descendant of another node, and if so, returns 1

Returns all nodes at the specified level:

-- Returns all nodes at the specified level
declare @NodeId    hierarchyid

select @NodeId=NodeId
from RoleMan 
where Par_NodeId=1        -- Designated Level 1

select @NodeId
select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where NodeId.GetAncestor(0)=@NodeId    -- GetAncestor(0)，Returns the data of the current node at the current level

select @NodeId
select *,NodeId.ToString() NodeId_Path 
from RoleMan 
where NodeId.GetAncestor(1)=@NodeId    -- GetAncestor(1)，Returns the specified level(@NodeId Data for all nodes at the next level specified as Level 1
                                    -- Number 1 indicates the number of levels to be dropped in the hierarchy.

Insert a new node:

declare @PNodeId hierarchyid
declare @NodeId    hierarchyid

select @PNodeId=NodeId 
from RoleMan 
where RoleId=3        -- Get Administrator B Node, which is used to specify a parent for the added node

select @NodeId=NodeId 
from RoleMan 
where RoleId=7        -- Getting Users BB Node, which specifies which child node is behind the added node

insert into RoleMan(NodeId,RoleId,RoleName)　　
values(@PNodeId.GetDescendant(@NodeId, NULL),'8','user BC')　　--That is, the parent node is 'Administrators B' The following child nodes 'user BB' Add a node later 'user BC'

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

Of course, this is when there are children under the parent node, so how should we add them when there are no children under the parent node? Just set the two parameters of GetDescendant(null,null) to null. As follows:

declare @PNodeId hierarchyid

select @PNodeId=NodeId 
from RoleMan 
where RoleId=8        -- Getting Users BC Node, which is used to specify a parent for the added node

insert into RoleMan(NodeId,RoleId,RoleName)
values(@PNodeId.GetDescendant(null, NULL),'9','user BCA')    -- Adding child nodes to parent nodes without child nodes

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

If you need to insert a child between two children below a parent node, you need to specify two parameters of GetDescendant(@Child1,@Child2). As follows:

declare @PNodeId hierarchyid
declare @Child1    hierarchyid
declare @Child2 hierarchyid

select @PNodeId=NodeId 
from RoleMan 
where RoleId=2        -- Get Administrator A Node, which is used to specify a parent for the added node

select @Child1=NodeId  
from RoleMan 
where RoleId=4        -- Get the first child node

select @Child2=NodeId  
from RoleMan 
where RoleId=5        -- Get the second child node

insert into RoleMan(NodeId,RoleId,RoleName)
values(@PNodeId.GetDescendant(@Child1, @Child2),'10','user A Queue jumping')-- Administrator at parent node A Subnode users AA And users AB Insert a node user between A Queue jumping

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

Change node location:

The GetReparentedValue method should be used to change the location of the node. This method takes two parameters, one is the hierarchyid of the original node and the other is the hierarchyid of the target node.

-- Administrator B Subnode users under nodes BA Move to Administrator A Subnode Users of Nodes AB behind
declare @RawNodePath hierarchyid
declare @NewNodePath hierarchyid

select @RawNodePath=NodeId  
from RoleMan 
where RoleId=6        -- Getting node users BA 

select @NewNodePath=NodeId 
from RoleMan 
where RoleId=2        -- Get the Node Administrator A

select @NewNodePath=@NewNodePath.GetDescendant(MAX(NodeId), NULL)    -- Get the Node Administrator A The largest child node below, the last child node
from RoleMan 
where NodeId.GetAncestor(1)=@NewNodePath    -- Get the parent node administrator A All the sublevels below

update RoleMan 
set NodeId=NodeId.GetReparentedValue(@RawNodePath, @NewNodePath)
where NodeId.IsDescendantOf(@RawNodePath) = 1 

select *,
NodeId.ToString() NodeId_Path    
from RoleMan

go

hierarchyid function:

GetLevel(): Used to determine the depth of the current level (level), the top level (root node) is 0, and then add 1 in turn.

ToString(): Because the value of the hierarchyid type is expressed in hexadecimal, ToString() is used to convert the hierarchyid type to a string type.

IsDescendantOf(): Determines whether the specified node is a descendant of another node, and if so, returns 1. A parameter for the specified node.

GetAncestor(n): n=0 returns the data of the current node at the current level. Otherwise, all nodes of the lower N-level at the specified level will be returned.

GetDescendant(null,null): Used to add nodes, this method accepts two parameters, which can be null, the first child node, the second child node. If the first parameter is not empty, it means adding a node after the child node under the specified parent node, and if both parameters are empty, it means adding a node to the node without the child node.

GetReparentedValue(): Used to change the location of a node, this method takes two parameters, one is the hierarchyid of the original node, the other is the hierarchyid of the target node.

GetRoot(): Gets the root of the node.

Parse(): Converts a string to hierarchyid. The format of the string is usually / 1 / like this.

Read(): Read reads the binary representation of SqlHierarchyId from the incoming BinaryReader and sets the SqlHierarchyId object to that value. Read cannot be invoked using Transact-SQL. Please use CAST or CONVERT instead.

Write(): Write writes the binary representation of SqlHierarchyId to the incoming BinaryWriter. Write cannot be invoked by using Transact-SQL. Please use CAST or CONVERT instead.

Hierarchy index strategy:

There are two strategies for indexing hierarchical data: depth first and breadth first.

Depth-first index, the storage location of each row in the subtree is adjacent, in short, it is stored in hierarchy-based value order.

Create a depth-first index:

--Creating Depth First Index
create unique index Role_Depth_First 
on RoleMan(NodeId) 
 
go

select *,NodeId.ToString() NodeId_Path
from RoleMan 
order by NodeId

The breadth-first index is to store the rows at each level in the hierarchy together, in short, in a hierarchical order.

Create a breadth-first index:

--Create breadth-first index
create clustered index Role_Breadth_First   
on RoleMan(Par_NodeId,NodeId) ;  
go  

select *,NodeId.ToString() NodeId_Path
from RoleMan 
order by Par_NodeId,NodeId

Reference resources:

http://blog.csdn.net/zhanghongju/article/details/42584643

https://msdn.microsoft.com/zh-cn/library/bb677173.aspx

Topics: SQL Server SQL

Programmer Think

SQL Server uses Hierarchyid to manipulate hierarchical data

Hot Topics