MS SQL: hierarchyid - a new hierarchy
Nowadays, database systems have the highest prevalence of relational databases, in which the main objects are tables and relationships between them. Tables are very good, they allow you to solve most of the tasks of storing data and manipulating them. But in the real world, entities requiring storage are not always presented in tabular form. One of these very common types of data structure other than a table is a tree structure, where each data element has an ancestor and descendants. An example of such a structure can be the state structure of an enterprise, which is headed by a director (root of a tree), his deputies, departments with chiefs who report to certain deputies, employees of departments that report to heads.
One of the ways to keep such a structure in the table is to define an additional field for each entity, which will somehow determine the ancestor. Thus, we will always know the ancestor and by simple enumeration, we will be able to restore the entire tree of the hierarchy. This is a very common method and is used everywhere where you need to present a tree hierarchy in the tables.
However, the developers of the MS SQL DBMS in their new version of MS SQL 2008 offer a new type of hierarchyid data storage for implementing the tree hierarchy.
The hierarchyid type is a system database type, the size of which can vary depending on the tree structure (its depth) and the average number of descendants of the nodes. The following calculations are given in MSDN: for a tree with a hierarchy of 6 levels for 100,000 people, hierarchyid will occupy 38 bits, which the database rounds to 40 bits or 5 bytes. The maximum size that hierarchyid can occupy is 892 bytes.
')
To begin with, we will create a very simple table that will store the staff hierarchy in a certain company:
The table will contain: hierarchyid, employee id, and his name. Next, we will try to recreate the following staff hierarchy in this table:
First, create the root of the hierarchy:
Note hierarchyid :: GetRoot () is a static method that always returns the identifier of the root of the hierarchy.
Next, add the descendants to the root record:
The following code sections are noteworthy in this code:
Finish the picture by filling in the table with all the remaining entries:
After we execute all this code we will get the following picture
Obviously, the selected data accurately copies the structure of the hierarchy and in order to get the correctly sorted data we did not have to resort to some tricks that are necessary when the hierarchy is implemented in the conventional way through the field with parentId.
MS SQL 2008 offers several functions for working with hierarchyid. Here I briefly give the purpose of each of them, and for a full description I suggest to refer to the documentation:
MS SQL 2008 offers a new way to store data that is a tree hierarchy. For these purposes, the hierarchyid type is introduced, which contains a “path” to the hierarchy element. To help the database programmer, a set of auxiliary functions is offered that allow you to organize the presentation of the hierarchy, the movement of data, access to the elements and the addition of new hierarchy elements.
The proposed option, in my opinion, is an excellent replacement for the standard method of storing hierarchical data through the parentId field (ancestor). Using hierarchyid allows you to create hierarchical structures with minimum code writing.
One of the ways to keep such a structure in the table is to define an additional field for each entity, which will somehow determine the ancestor. Thus, we will always know the ancestor and by simple enumeration, we will be able to restore the entire tree of the hierarchy. This is a very common method and is used everywhere where you need to present a tree hierarchy in the tables.
However, the developers of the MS SQL DBMS in their new version of MS SQL 2008 offer a new type of hierarchyid data storage for implementing the tree hierarchy.
Introduction
The hierarchyid type is a system database type, the size of which can vary depending on the tree structure (its depth) and the average number of descendants of the nodes. The following calculations are given in MSDN: for a tree with a hierarchy of 6 levels for 100,000 people, hierarchyid will occupy 38 bits, which the database rounds to 40 bits or 5 bytes. The maximum size that hierarchyid can occupy is 892 bytes.
')
Creating a table
To begin with, we will create a very simple table that will store the staff hierarchy in a certain company:
CREATE TABLE Table_1 (
hid hierarchyid NOT NULL ,
userId int NOT NULL ,
userName nvarchar (50) NOT NULL ,
CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED
(
[hid] ASC
)) * This source code was highlighted with Source Code Highlighter .
The table will contain: hierarchyid, employee id, and his name. Next, we will try to recreate the following staff hierarchy in this table:
1 Ivanov
2 Petrov
7 Smirnov
8 Pupkin
3 Sidorov
4 Vasechkin
5 Kruglov
6 Squares
Creating a hierarchy
First, create the root of the hierarchy:
insert into Table_1
values (hierarchyid :: GetRoot (), 1, 'Ivanov' ) * This code was highlighted with Source Code Highlighter .
Note hierarchyid :: GetRoot () is a static method that always returns the identifier of the root of the hierarchy.
Next, add the descendants to the root record:
declare Id hierarchyid
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = hierarchyid :: GetRoot ()
insert into Table_1
values (hierarchyid :: GetRoot (). GetDescendant (@id, null ), 2, 'Petrov' );
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = hierarchyid :: GetRoot ()
insert into Table_1
values (hierarchyid :: GetRoot (). GetDescendant (@id, null ), 3, 'Sidorov' );
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = hierarchyid :: GetRoot ()
insert into Table_1
values (hierarchyid :: GetRoot (). GetDescendant (@id, null ), 4, 'Vasechkin' );
* This source code was highlighted with Source Code Highlighter .
The following code sections are noteworthy in this code:
- hid.GetAncestor (1) = hierarchyid :: GetRoot () - selects all records whose ancestor (direct) is the root;
- hierarchyid :: GetRoot (). GetDescendant (@id, null) - selects the first free hierarchyid direct descendants of a tree root. Description of the parameters I give below.
Finish the picture by filling in the table with all the remaining entries:
declare @phId hierarchyid
select @phId = ( SELECT hid FROM Table_1 WHERE userId = 2);
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = @phId
insert into Table_1
values (@ phId.GetDescendant (@id, null ), 7, 'Smirnov' );
select @phId = ( SELECT hid FROM Table_1 WHERE userId = 4);
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = @phId
insert into Table_1
values (@ phId.GetDescendant (@id, null ), 5, 'Kruglov' );
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = @phId
insert into Table_1
values (@ phId.GetDescendant (@id, null ), 6, 'Squares' );
select @phId = ( SELECT hid FROM Table_1 WHERE userId = 7);
select Id = MAX (hid)
from Table_1
where hid.GetAncestor (1) = @phId
insert into Table_1
values (@ phId.GetDescendant (@id, null ), 8, 'Pupkin' );
* This source code was highlighted with Source Code Highlighter .
Please note that all the code in the article should be executed in one script.
After we execute all this code we will get the following picture
select hid.ToString (), hid.GetLevel (), * from Table_1 * This has been highlighted with the Source Code Highlighter .
/ 0 0x 1 Ivanov / 1/1 0x58 2 Petrov / 1/1/2 0x5AC0 7 Smirnov / 1/1/1/3 0x5AD6 8 Pupkin / 2/1 0x68 3 Sidorov / 3/1 0x78 4 Vasechkin / 3/1/2 0x7AC0 5 Kruglov / 3/2/2 0x7B40 6 Squares WIN-Z6U4ALRNDSU (WIN-Z6U4ALRNDSU \ Administrator): (8 row (s) affected)
Obviously, the selected data accurately copies the structure of the hierarchy and in order to get the correctly sorted data we did not have to resort to some tricks that are necessary when the hierarchy is implemented in the conventional way through the field with parentId.
Secondary functions
MS SQL 2008 offers several functions for working with hierarchyid. Here I briefly give the purpose of each of them, and for a full description I suggest to refer to the documentation:
- GetAncestor - gives the hierarchyid ancestor, you can specify the level of the ancestor, for example, 1 selects the immediate ancestor;
- GetDescendant - returns a hierarchyid child, takes two parameters with which you can control which child you need to get at the output;
- GetLevel - returns the hierarchyid level;
- GetRoot - returns the root level;
- IsDescendant - checks if hierarchyid passed through a parameter is a descendant;
- Parse - converts a hierarchyid string representation to hierarchyid itself;
- Reparent - allows you to change the current ancestor;
- ToString - converts hierarchyid to string representation.
Conclusion
MS SQL 2008 offers a new way to store data that is a tree hierarchy. For these purposes, the hierarchyid type is introduced, which contains a “path” to the hierarchy element. To help the database programmer, a set of auxiliary functions is offered that allow you to organize the presentation of the hierarchy, the movement of data, access to the elements and the addition of new hierarchy elements.
The proposed option, in my opinion, is an excellent replacement for the standard method of storing hierarchical data through the parentId field (ancestor). Using hierarchyid allows you to create hierarchical structures with minimum code writing.
Source: https://habr.com/ru/post/27774/
All Articles