This article addresses the meaning and usage of hierarchyid data type and the method used in SQL Server.
Hierarchyid can be defined as a system data type or a variable length. As the name suggests it is used for representing different positions in a hierarchy. However, column type of hierarchyid doesn’t automatically represent a hierarchy tree. It completely depends on the application, to assign and generate hierarchyid values in a way that the values are able to reflect their relationship with rows. Any data where one item can be considered as a parent of any other item forms a Hierarchical relationship.
Hierarchyid data type value represents a position in the hierarchy tree. Here are some of the basic properties of hierarchyid values:
Extremely Compact
Hierarchyid data type compacts the data into small bits which can significantly help in storing the data effectively in the system. On an average, the bits required for representing a tree node for X nodes depends on the fanout, the average subdivision or related values of a node. So, if an organization with a hierarchy of over 100,000 people with a fanout of 6 levels will only take about 38 bits.
Depth – First Order Comparison
If two hierarchyid are given X and Z, X < Z, this means that X will come before in the depth-first traversal of the hierarchy tree. All index of hierarchy data type is stored in depth-first order. And nodes which are placed in depth-first traversal close to each other are also stored near each other. For example, the employees of a department will be stored adjacent to their management record.
Arbitrary Deletion and Insertion
GetDescendant method allows users to generate sibling in the right/left section or in between any node or two sibling nodes. The system also ensures to maintain the comparison property, even when a node is deleted or inserted in the hierarchy. Most deletions and insertions are preserved by the compactness property. However, if a user inserts between two existing nodes, it will produce hierarchyid values with a slightly un-compact representation.
The encoding which is used in hierarchyid type is restricted to 892 bytes. So, nodes with a higher range of representation which exceeds 892 bytes won’t be represented by hierarchyid type.
Data Type Conversion
Users can convert hierarchyid data type into other data types using the following methods:
- The user can use ToString () method for converting the hierarchyid value to a logical representation by a nvarchar(4000) data type.
- In order to covert hierarchyid into varbinary, users can use Read () and Write () data types.
- Users need to convert Hierarchyid data type into strings before transmitting hierarchyid parameters via SOAP.
Hierarchyid type uses logic to encode information of a single node from the hierarchy tree by encoding its path through all channels starting from its root till its node. This path creates a logically represented sequence, where all nodes follow a standard path which starts from the root. The comparison between these children is conducted based on their integer sequences which are separated by dots and are represented in a dictionary order. Hierarchyid is an easy way to create queries and store data in hierarchical order.
If you are running a commercial application on SQL Server, you need to make sure that you thoroughly address data loss scenarios arising out of a database crash. Consider getting a state of the art sql repair tool.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com
