TSQL HierarchyID Data Type usage (sqlserver2008 above has this data type)

TSQL HierarchyID Data Type usageApril 25, 2017 09:47:46Number of readings:

The HierarchyID data type is a variable-length system data type. You can use HierarchyID to represent locations in a hierarchy. columns of type HierarchyID do not automatically represent trees, and the application generates and assigns HierarchyID values so that the desired relationship between rows and rows is reflected in these values.

1, string representation

Must start with "/", End With "/", use a numeric value between "/" to identify an element, generally integer or decimal, for example "/", "/1/2/", "/1/2/3/", "/1/2/3.1"

2, conversion of data types

DECLARE @sa Nvarchar declare @ha hierarchyiddeclare @hb hierarchyidset @sa = '/1/2/3/' Set @ha =hierarchyid::P Arse (@sa ) Set @hb =0x5b5eselect @sa as SA, @ha as Ha, @hb. ToString () as HB

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 @ha hierarchyiddeclare @hb hierarchyidset @sa = '/1/2/3/' Set @sb = '/1/2/' Set @ha =hierarchyid::P arse (@sa) set @hb =hierarchyid::P arse (@sb) if @ha >[email protected]print @sa + ' >= ' [ Email protected]else print @sa + ' < ' [email protected]

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 @ha hierarchyidset @sa = '/1/2/3/' Set @ha =hierarchyid::P arse (@sa) Select @sa as SA, @ha as H A, @ha. Getlevel () AS Level

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

Select Hierarchyid::getroot (). ToString () as Tootstring,hierarchyid::getroot () 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 @ha hierarchyiddeclare @hb Hierarchyiddeclare @hr hierarchyidset @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 ()

If Leftchild is '/1/2/3 ', Rightchild is '/1/2/4 ', need to insert a new node between these two nodes, how do I handle it? The number representing the node, and does not necessarily have to be a positive integer, decimals can also, such as, newchild= '/1/2/ 3.1/';

DECLARE @sa Nvarchar declare @sb Nvarchar (+) declare @sr Nvarchar (+) declare @ha hierarchyiddeclare @hb Hierarchyiddeclare @hr hierarchyidset @sa = '/1/2/3/' Set @sb = '/1/2/4/' 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 @ha hierarchyiddeclare @hb Hierarchyiddeclare @hr hierarchyidset @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)

8, to update the value of HierarchyID, the value of the child nodes associated with the node must be updated cascade, due to the limitations of the HierarchyID type itself.

The HierarchyID data types have the following limitations:

• Columns of type HierarchyID do not automatically represent trees. The application generates and assigns HierarchyID values so that the desired relationship between the rows and rows is reflected in these values. Some applications may have columns of type HierarchyID that indicate the location in a hierarchy defined in another table.
• A concurrency condition that is managed by the application to manage the generation and allocation of HierarchyID values. The HierarchyID value in a column cannot be guaranteed to be unique unless the application uses a unique key constraint or the application itself enforces uniqueness through its own logic.
• The hierarchical relationship represented by the HierarchyID value is not enforced like a foreign key relationship. The following hierarchical relationship may occur and sometimes the relationship is justified: A has child B, then a is removed, resulting in a relationship between B and a record that does not exist. If this behavior is unacceptable, the application must first query for descendants before deleting the parent

8.1. Create a data source

CREATE TABLE DBO.EMPH2 (Idpath HierarchyID NOT NULL primary KEY,ID int. not Null,parentid as Idpath. GetAncestor (1) Persisted foreign key references Dbo.emph2 (idpath), descr varchar (100))

Idpath= '/1/2/6/' descendant nodes such as

Select E.idpath.tostring () as idpath,e.id,e.parentid.tostring () as Parentidpath,e.descrfrom Dbo.emph2 e where E.idpath.isdescendantof (HierarchyID::P arse ('/1/2/6/')) =1

8.2, the child node is changed to another parent node, such as deleting the node of idpath= '/1/2/6/' and changing the parent node of its child node to idpath= '/1/2/7/'

Because of a foreign key relationship, you must first change the parent node of the child node, and then delete the node idpath= '/1/2/6/'.

--delete child Notes--select e.idpath.tostring () as idpath,e.id,e.parentid.tostring () as Parentidpath,e.descrupdate E Set E.idpath=hierarchyid::P arse ('/1/2/7/' +cast (e.id as varchar) + '/') from DBO.EMPH2 ewhere E.idpath.isdescendantof ( HierarchyID::P arse ('/1/2/6/')) =1 and E.idpath!=hierarchyid::P arse ('/1/2/6/')--delete parent Notedelete DBO.EMPH2 where Idpath=hierarchyid::P arse ('/1/2/6/')--checkselect e.idpath.tostring () as idpath,e.id,e.parentid.tostring () as Parentidpath,e.descrfrom dbo.emph2 ewhere e.idpath.isdescendantof (HierarchyID::P arse ('/1/2/7/')) =1

8.3, the parent node of idpath= '/1/2/6/' node is changed, and its child node is still its child node.

The idea is to create a new node and hang the child nodes under the new node.

--create new Nodeinsert into DBO.EMPH2 (IDPATH,ID,DESCR) Select HierarchyID::P arse ('/1/3/6/'), Id,descrfrom DBO.EMPH2 Ewhere E.idpath=hierarchyid::P arse ('/1/2/6/')--delete child Notes--select e.idpath.tostring () as IDPath,e.id, E.parentid.tostring () as Parentidpath,e.descrupdate e set E.idpath=hierarchyid::P arse ('/1/3/6/' +cast (e.id as varchar) + '/') from Dbo.emph2 ewhere e.idpath.isdescendantof (HierarchyID::P arse ('/1/2/6/')) =1 and E.idpath!=hierarchyid:: Parse ('/1/2/6/')--delete parent notedelete dbo.emph2 where Idpath=hierarchyid::P arse ('/1/2/6/')--checkselect E.idpath.tostring () as idpath,e.id,e.parentid.tostring () as Parentidpath,e.descrfrom dbo.emph2 Ewhere E.idpath.isdescendantof (HierarchyID::P arse ('/1/3/6/')) =1

8.4 Directional insertion of new nodes, due to the sequence of nodes, using Getdescendant (childleft,childright) to guarantee the order.

Insert a new child node between the node idpath= '/1/2/6/' child nodes id=15,id=16, the new child node id=36,descr= ' E1136 ', the idea is to use Getdescendant (Childleft, Childright) Gets the new Idpath and then inserts it into the table.

DECLARE @id intdeclare @descr Nvarchar (+) declare @sa Nvarchar (+) declare @sb Nvarchar (+) declare @sr Nvarchar (100) DECLARE @hnew hierarchyidset @id =36set @descr = ' E1136 ' Set @sa = '/1/2/6/15/' Set @sb = '/1/2/6/16/' Set @sr = '/1/2/6/' SET @ hnew= HierarchyID::P arse (@sr). Getdescendant (HierarchyID::P arse (@sa), HierarchyID::P arse (@sb)) insert into DBO.EMPH2 (IDPATH,ID,DESCR) VALUES (@hnew , @id, @descr)

Select E.idpath.tostring () as idpath,e.id,e.parentid.tostring () as Parentidpath,e.descrfrom dbo.emph2 Ewhere E.idpath.isdescendantof (HierarchyID::P arse ('/1/2/6/')) =1order by E.idpath

From the sorted result set, it can be seen that the id=36 node, between the nodes in Id=15 and ID=16, is implemented in order by Getdescendant (Childleft,childright).

9, HierarchyID type of data, it is easy to achieve breadth-first traversal and depth-first traversal

9.1, breadth-first traversal refers to the same level of nodes in the query hierarchy

Select Idpath. ToString () as Idpath,id,parentid. ToString () as PARENTIDPATH,DESCR from Dbo.emph2 where Idpath. Getlevel () =2

9.2, Depth-first traversal refers to traversing all child nodes of a node

Select Idpath. ToString () as Idpath,id,parentid. ToString () as PARENTIDPATH,DESCR from Dbo.emph2 where Idpath. Isdescendantof (HierarchyID::P arse ('/1/2/6/')) =1

TSQL HierarchyID Data Type usage (sqlserver2008 above has this data type)