To resolve a new data type in a SQL Server 2008 database

For relational databases, a hierarchical structure that behaves like a tree is always a problem. Microsoft first tried to solve this problem in SQL Server 2005, which is what is called a universal Datasheet expression (Common table expressions,cte) implementation.

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

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

A HierarchyID type of field stores the exact location of the record in the hierarchy. Denny Cherry provides an example that shows 0x, 0x58, and 0x5ac0 three values, their string representations are "/", "/1/" and "/1/1/" respectively. This raises questions about consistency and performance, especially when the parent record is changed. Ravi S.maniam recommends using this design approach when changing the parent record operation infrequently.

There are also a series of functions that appear at the same time as the HierarchyID type. GetAncestor and Getdescendant methods can be used to traverse trees. The ToString and parse methods are used to convert a HierarchyID type binary to a string representation. Oddly enough, there are some ways to support BinaryReader and BinaryWriter.

It's a strange way to look back on getdescendant. It does not actually return those child records, but instead returns the location of those potential child nodes. When inserting a new record into the tree, we must call the Getdescendant method to get the location of the last child node of the specified parent record before we can get the space immediately following that position.

To date, there are no suitable examples of using T-SQL to obtain a tree structure. In fact, everything associated with it is more like a command, not a collection based operation.