Chapter 29. Effective use of HierarchyId
We find hierarchies in many places in our lives, such as organizational structures, sporting events, product categories in an e-market, social relationships, algorithms, classifications, and elsewhere. Modeling such systems means creating and storing information about the objects in a system. Tons of code was written using object-oriented programming principles, and many variants of storing information about objects in a database were invented. Hierarchy is a type of relationship between objects, and that’s why modeling hierarchies is a common operation in writing code and databases.
There are many types of hierarchy implementations in a database. Parent-child relationships are one of the most common and at the same time the most native type of hierarchy modeling. Each row must have one parent row (parentId field related to the parent key) and several children rows (parentId field related to the current row key) in this implementation. It looks simple, but usually the query is recursive on this hierarchy, and data selection can have complex logic. This can cause a huge performance overhead and performance problems.
XML is another common implementation of hierarchy in a database. Using XML for hierarchy design is also native, but querying XML data in SQL has a poor track record.
