Hierarchyid Data Type

In the past, when we established a tree structure in a relational database, we usually used ID + ParentID to implement the parent-child relationship between two records. However, this method can only indicate its relative position. Before the emergence of SqlServer2005, you usually use cursors to solve these problems. However, anyone familiar with the internal database mechanism knows the performance problems and other problems caused by using cursors.

In the past, when we established a tree structure in a relational database, we usually used ID + ParentID to implement the parent-child relationship between two records. However, this method can only indicate its relative position. Before the emergence of SqlServer2005, we usually use cursors to solve these problems. However, anyone familiar with the internal database mechanism knows the performance problems and other problems caused by using cursors.

In the past, when we established a tree structure in a relational database, we usually used ID + ParentID to implement the parent-child relationship between two records. However, this method can only indicate its relative position. Before the emergence of SqlServer2005, you usually use cursors to solve these problems. However, anyone familiar with the internal database mechanism knows that the performance problems caused by using cursors and other problems are serious.

In SqlServer2005, you can choose to use CTE for Recursive queries. This method is more concise, but because the database uses recursive queries internally, its efficiency is still not high; to make the query concise and efficient, we usually add redundant fields, such as adding a "Path" field. Fuzzy queries are used for left matching during query. After creating an index on the Path, the query efficiency is still quite high, so this method is also a conventional design method;

SQL SERVER 2008 introduces a new hierarchyid data type, which can be used for local storage and manage its location in the tree hierarchy. this function is used to represent the position in the hierarchy. some built-in function methods provided by this function can operate and traverse hierarchies, making it easier to store and query hierarchical data, instead of having to recursively retrieve it through CTE.

Hierarchyid is actually a CLR custom data type that is opened in sequence: database> System database> master> programmable> type> system data type> CLR data type> hierarchyid.

Some functions related to hierarchyid mainly include:

• GetAncestor: obtains the ancestor of a certain level.
• GetDescendant: Get the child of a certain level
• GetLevel: Get level
• GetRoot: Get the root
• IsDescendantOf: determines whether a node is a child of a node.
• Parse: converts a string to hierarchyid. The string format is usually/1 /.
• Read: Read the binary representation of SqlHierarchyId from the incoming BinaryReader, and set the SqlHierarchyId object to this value. You cannot use Transact-SQL to call Read. Use CAST or CONVERT instead.
• GetReparentedValue: can be used to move nodes (or Subtrees)
• ToString: converts hierarchyid to a string, which is the opposite of parse.
• Write: Write the binary representation of SqlHierarchyId to the passed-In BinaryWriter. You cannot use Transact-SQL to call Write. Use CAST or CONVERT instead.

The value of hierarchyid indicates the position in the tree hierarchy. The value of hierarchyid has the following attributes:

• Very compact

  InNIndicates that the average number of bits required for a node depends on the average number of ends (the average sublevel of a node ). The number of terminals is relatively small (0-7), and the size is about 6 * logANBit, where A is the average number of ends. For an organizational hierarchy with an average of 6 levels and 100,000 people, a node occupies about 38 places. During storage, this value is rounded up to 40 bits, that is, 5 bytes.

• Compare by depth priority

  Given two hierarchyid valuesAAndB,A Indicates that the depth of the tree is prioritized. First, locate a and then find B. Hierarchyid data-type indexes are sorted in depth-first order. In depth-first traversal, the storage locations of adjacent nodes are also adjacent. For example, the sub-level storage location of a record is adjacent to the storage location of the record.

• Supports arbitrary insertion and deletion.

  By using the GetDescendant method, you can always generate a peer node between the right, left, or any two peer nodes of any given node. When any number of nodes are inserted or deleted in the hierarchy, the comparison attribute remains unchanged. Most insert and delete operations retain the Compact attributes. However, for the insert operation between two nodes, the representation of the hierarchyid value will be slightly less compact.

Hierarchyid data types have the following limitations:

• Columns whose class type is hierarchyid do not automatically represent trees. Hierarchyid values are generated and allocated by the application so that the relationships between rows are reflected in these values. Some applications may not even need to use columns of the hierarchyid type to represent the tree. These values may be references to locations in hierarchies defined in other tables.

• The application manages the concurrency when hierarchyid is generated and assigned. There is no guarantee that the hierarchyid value in the column is unique unless the application uses a unique key constraint or the application itself enforces uniqueness through its own logic.

• The hierarchical relationship represented by hierarchyid is not as forced as the foreign key relationship. The following hierarchical relationship may occur, and sometimes this relationship is reasonable: A has sublevel B and then deletes A, resulting in A relationship between B and A non-existent reCord. If this behavior is unacceptable, the application must first query whether the parent has any descendant before deleting the parent.

There are two policies for indexing hierarchical data:

• Depth first

  Deep-priority index. The storage locations of each row in the subtree are adjacent. For example, all employees managed by a manager are stored near their manager's records.

• Breadth First

  Breadth First stores each level of rows in the hierarchy. For example, records of employees directly under the same manager are stored in adjacent locations.

For example, the following example is a staff table. The data has the following relationship:

Scott

|

Mark <-> Ravi

|

Ben <-> Laura Vijay <-> Frank <-> James

Use AdventureWorksLT Go --Scheme Creation Create Schema HumanResources Go --Table Creation CREATE TABLE HumanResources.EmployeeDemo (OrgNode HIERARCHYID,EmployeeID INT,LoginID VARCHAR(100),Title VARCHAR(200),HireDate DATETIME) Go --Index Creation CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo ON HumanResources.EmployeeDemo (OrgNode,EmployeeID)

Insert some data below

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  LUES (hierarchyid::GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ;       CLARE @Manager hierarchyid    LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)     LUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark', 'CTO', '4/05/07')        CLARE @Manager hierarchyid CLARE @FirstChild hierarchyid  LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; lect @FirstChild = @Manager.GetDescendant(NULL,NULL)        SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)        LUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi', 'Director Marketing', '4/08/07')                   Insert the First Descendant of a Child Node    CLARE @Manager hierarchyid     LECT @Manager = CAST('/1/' AS hierarchyid)       SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) LUES (@Manager.GetDescendant(NULL, NULL),45, 'adventure-works\Ben','Application Developer', '6/11/07') ;           Insert the Second Descendant of a Child Node CLARE @Manager hierarchyid        CLARE @FirstChild hierarchyid         LECT @Manager = CAST('/1/' AS hierarchyid)   LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)    SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)      LUES (@Manager.GetDescendant(@FirstChild, NULL),55, 'adventure-works\Laura','Trainee Developer', '6/11/07') ;                      Insert the first node who is the Descendant of Director Marketing      CLARE @Manager hierarchyid     CLARE @FirstChild hierarchyid     LECT @Manager = CAST('/2/' AS hierarchyid)          SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)   LUES (@Manager.GetDescendant(NULL, NULL),551, 'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;                  Insert the second node who is the Descendant of Director Marketing     CLARE @Manager hierarchyid           CLARE @FirstChild hierarchyid        LECT @Manager = CAST('/2/' AS hierarchyid)   LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) LUES (@Manager.GetDescendant(@FirstChild, NULL),531, 'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;             Insert the third node who is the Descendant of Director Marketing in between 2 existing descendants    CLARE @Manager hierarchyid    CLARE @FirstChild hierarchyid      CLARE @SecondChild hierarchyid     LECT @Manager = CAST('/2/' AS hierarchyid)          LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)   LECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)   SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  LUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543, 'adventure-works\james','Manager Consumer Sales', '12/04/06') ;           

The Hierarchyid field type provides a series of related query functions to conveniently query parent-child relationship data. Next we will query the data

DECLARE @ TID hierarchyid SELECT @ TID = OrgNode FROM HumanResources. employeeDemo WHERE title = 'cto' SELECT *, OrgNode. getLevel () as level, OrgNode. toString () as path FROM HumanResources. employeeDemo WHERE @ TID. isDescendantOf (OrgNode) = 1 SELECT *, OrgNode. getLevel () as level, OrgNode. toString () as path FROM HumanResources. employeeDemo WHERE OrgNode. isDescendantOf (@ TID) = 1

The following are a few stored procedures:

• Insert record to table

SET QUOTED_IDENTIFIER ON GO --Use Serializable Transaction CREATE PROCEDURE [dbo].[AddEmployee](@ManagerID hierarchyid, @EmpID int,@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime) AS BEGIN DECLARE @LastChild hierarchyid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo WHERE OrgNode = @ManagerID INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate) COMMIT END ;

• Mobile hierarchical relationship

CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) ) AS BEGIN DECLARE @nold HierarchyID DECLARE @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 @nold.IsDescendantOf(OrgNode) = 1 COMMIT TRANSACTION END 

• Obtain the largest subnode and pass it to the GetDescendant () function to obtain the new subnode.

Create Function GetMyMaxChild(@ManagerID as BigInt) Returns HierarchyID BEGIN Declare @ManagerNode HierarchyID Declare @MaxChild HierarchyID  --Get the ManagerNode Select @ManagerNode = OrgNode from HumanResources.EmployeeDemo Where EmployeeID = @ManagerID  --Get the Max Child Select @MaxChild = Max(OrgNode) from HumanResources.EmployeeDemo Where OrgNode.GetAncestor(1) = @ManagerNode --Return the Value RETURN @MaxChild END 

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

Http://nibblersrevenge.cluss.de/archive/2009/05/31/how-to-use-hierarchyid-in-linqtosql-or-entity-framework-mssql.aspx

Http://www.cnblogs.com/downmoon/archive/2011/05/03/2035259.html

<无>

Use AdventureWorksLT Go  --Scheme Creation Create Schema HumanResources Go  --Table Creation CREATE TABLE HumanResources.EmployeeDemo (OrgNode HIERARCHYID,EmployeeID INT,LoginID VARCHAR(100),Title VARCHAR(200),HireDate DATETIME)Go --Index Creation CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo ON HumanResources.EmployeeDemo (OrgNode,EmployeeID)

SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  LUES (hierarchyid::GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ;       CLARE @Manager hierarchyid    LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)     LUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark', 'CTO', '4/05/07')        CLARE @Manager hierarchyid CLARE @FirstChild hierarchyid  LECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo; lect @FirstChild = @Manager.GetDescendant(NULL,NULL)        SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)        LUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi', 'Director Marketing', '4/08/07')                   Insert the First Descendant of a Child Node    CLARE @Manager hierarchyid     LECT @Manager = CAST('/1/' AS hierarchyid)       SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) LUES (@Manager.GetDescendant(NULL, NULL),45,   'adventure-works\Ben','Application Developer', '6/11/07') ;           Insert the Second Descendant of a Child Node CLARE @Manager hierarchyid        CLARE @FirstChild hierarchyid         LECT @Manager = CAST('/1/' AS hierarchyid)   LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)    SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)      LUES (@Manager.GetDescendant(@FirstChild, NULL),55,  'adventure-works\Laura','Trainee Developer', '6/11/07') ;                      Insert the first node who is the Descendant of Director Marketing      CLARE @Manager hierarchyid     CLARE @FirstChild hierarchyid     LECT @Manager = CAST('/2/' AS hierarchyid)          SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)   LUES (@Manager.GetDescendant(NULL, NULL),551, 'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;                  Insert the second node who is the Descendant of Director Marketing     CLARE @Manager hierarchyid           CLARE @FirstChild hierarchyid        LECT @Manager = CAST('/2/' AS hierarchyid)   LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) LUES (@Manager.GetDescendant(@FirstChild, NULL),531, 'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;             Insert the third node who is the Descendant of Director Marketing        in between 2 existing descendants    CLARE @Manager hierarchyid    CLARE @FirstChild hierarchyid      CLARE @SecondChild hierarchyid     LECT @Manager = CAST('/2/' AS hierarchyid)          LECT @FirstChild = @Manager.GetDescendant(NULL,NULL)   LECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)   SERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)  LUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,  'adventure-works\james','Manager Consumer Sales', '12/04/06') ;

DECLARE @ TID hierarchyid SELECT @ TID = OrgNode FROM HumanResources. employeeDemo WHERE title = 'cto' SELECT *, OrgNode. getLevel () as level, OrgNode. toString () as path FROM HumanResources. employeeDemo WHERE @ TID. isDescendantOf (OrgNode) = 1 SELECT *, OrgNode. getLevel () as level, OrgNode. toString () as path FROM HumanResources. employeeDemo WHERE OrgNode. isDescendantOf (@ TID) = 1

SET QUOTED_IDENTIFIER ONGO--Use Serializable Transaction CREATE PROCEDURE [dbo].[AddEmployee](@ManagerID hierarchyid, @EmpID int,@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime)AS BEGIN DECLARE @LastChild hierarchyid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTIONSELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo WHERE OrgNode = @ManagerID INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate) COMMIT END ;

CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) ) AS BEGINDECLARE @nold HierarchyID DECLARE @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 @nold.IsDescendantOf(OrgNode) = 1 COMMIT  TRANSACTIONEND

Create Function GetMyMaxChild(@ManagerID as BigInt) Returns HierarchyID BEGIN Declare @ManagerNode HierarchyID Declare @MaxChild HierarchyID  --Get the ManagerNode Select @ManagerNode = OrgNode from HumanResources.EmployeeDemo Where EmployeeID = @ManagerID  --Get the Max Child Select @MaxChild = Max(OrgNode) from HumanResources.EmployeeDemo Where OrgNode.GetAncestor(1) = @ManagerNode --Return the Value RETURN @MaxChild END