New data type in SQL Server: hierarchyid

Source: Internet
Author: User
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.

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.