In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

One of the ways to keep this structure in the table is to define additional fields for each entity that will somehow determine ancestor. Thus, we will always know the parent and the simple enumeration, will be able to recover all the tree hierarchy. This is a very common method, and it is used everywhere where you need to present a tree-like hierarchy tables.

However, the developers of MS SQL offering in its new version of MS SQL 2008 for the implementation of a new type of tree-like hierarchy of storage hierarchyid.

Type hierarchyid - is a system database type, the amount of which may vary depending on the structure of a tree (its depth) and the average number of offspring nodes. In MSDN, the following calculations: for the tree with the hierarchy in 6 levels to 100,000 hierarchyid takes 38 bits, which DB will round up to 40 bits, or 5 bytes. Maximum same size that can hold hierarchyid equal to 892 bytes.

‍