New data type in SQL Server: hierarchyid

For relational databases, tree-like hierarchy is always a problem. Microsoft attempted to solve this problem for the first time in SQL Server 2005, that is, the implementation of common table expressions (CTE.

Although CTE works well in the existing database architecture, Microsoft has found a way to use such hierarchies as a top-level concept. Therefore, in order to achieve this effect, they proposed a "hierarchid" data type in SQL Server 2008.

In a traditional hierarchy, a record only stores a reference pointing to its parent record, which allows a record to obtain its relative position in the hierarchy instead of its absolute position. Changing the parent data row Reference of a record is an atomic update operation, which does not affect any sub-records of the record.

A hierarchyid field stores the exact position of the record in the hierarchy. Denny cherry provides an example where three values, 0x, 0x58, and 0x5ac0, are displayed. Their character strings are expressed as "/", "/1/" and "/1/1 /". This raises consistency and performance issues, especially when the parent record is changed. Ravi S. maniam recommends that you use this design method when the operation of the parent record is not frequently changed.

There are also a series of functions in combination with hierarchyid. The getancestor and getdescendant methods can be used to traverse the tree. The tostring and parse methods are used to convert hierarchyid binary and string representation. The odd thing is that there are some methods to support binaryreader and binarywriter.

Let's look back at getdescendant, which is a bit odd. In fact, it does not return those subrecords, but returns the locations of potential subnodes. When inserting a new record into the tree, we must call the getdescendant method to obtain the location of the last sub-node of the specified parent record, and then obtain the gap following the position.

So far, no suitable examples of using T-SQL to get a tree structure have been used. In fact, everything related to it is more like some commands than set-based operations.