How to use HierarchyID data type in SQL Server using real world example

[@deaconjohnbeagan7159]

Exactly what I was looking for. Anticipated my insert question. Brief and clear. Many thanks!

[@SoftwareNuggets]

Great to hear! Thanks for taking the time to view this video and leaving a comments.

[@FernandoLopez-el8mp]

Thank you! Really interesting. Good job!

[@GladiusArmis]

how about some code?

[@SoftwareNuggets]

hey keith, here is step 1 source code, make sure you do step by step, because, some of the statements are FAIL test

[@SoftwareNuggets]

use mydb; drop table testHierarchy; create table testHierarchy ( emp_id int not null primary key, organizationNode hierarchyid not null, manager_id int ) create unique index UIK_testHierarchy_organizationNode on testHierarchy(organizationNode); insert into testHierarchy(emp_id, organizationNode, manager_id) values (1, '/', null), -- root node (10, '/1/', 1), -- must begin with a '/' and end with a '/' (20, '/2/', 1), (11, '/1/1/', 1), (12, '/1/2/', 1), (13, '/1/3/', 1), (21, '/2/1/', 1), (22, '/2/2/', 1), (23, '/2/3/', 1) -- FAIL FAIL FAIL FAIL --this will FAIL -- test unique key insert insert into testHierarchy(emp_id, organizationNode, manager_id) values (14, '/1/1/', 20); -- FAIL FAIL FAIL FAIL -- FAIL FAIL FAIL FAIL --this will FAIL --show error insert --test unique poorly formatted value insert into testHierarchy(emp_id, organizationNode, manager_id) values (23, '/2/4', 20); -- FAIL FAIL FAIL FAIL --run one test at a time --start test select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/2/' manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy order by organizationNode.GetLevel(), manager_id --end test --run one test at a time --start test declare @node hierarchyid = 0x68; set @node = CAST('/1/3/' as hierarchyid); select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/3/' manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy where organizationNode = @node --end test --run one test at a time --start test declare @node hierarchyid; set @node = CAST('/1/3/' as hierarchyid); select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/3/' manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy where organizationNode = @node --end test --run one test at a time --start test select cast(0x5BC0 as int) IntValue --end test -- FAIL FAIL FAIL FAIL -- this will FAIL select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/2/' cast(organizationNode as int) IntValue, -- convert hext to integer?? you cannot cast entire object as integer manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy order by organizationNode.GetLevel(), manager_id -- FAIL FAIL FAIL FAIL -- insert another layer of employees insert into testHierarchy(emp_id, organizationNode, manager_id) values (111, '/1/1/1/', 11), (112, '/1/1/2/', 11), (113, '/1/1/3/', 11);

[@SoftwareNuggets]

step 2 use MyDB; --run one test at a time --start test select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/2/' manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy order by organizationNode.GetLevel(), manager_id -- get the parent of the employee = 10 ..... 0x select organizationNode.GetAncestor(1) Parent, emp_id from testHierarchy where emp_id = 10; -- end test --run one test at a time --start test select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/2/' manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy order by organizationNode.GetLevel(), manager_id -- get the parent of the employee = 11 ..... 0x58 select organizationNode.GetAncestor(1) Parent, emp_id from testHierarchy where emp_id = 11; -- end test --run one test at a time --start test select emp_id, organizationNode, organizationNode.ToString(), -- Returns a string with the logical representation '/1/2/' manager_id, organizationNode.GetLevel() -- Returns an integer that represents the depth of the node in this tree from testHierarchy order by organizationNode.GetLevel(), manager_id -- get the grandparent of employee = 11 .... 0x select organizationNode.GetAncestor(2) Parent, emp_id from testHierarchy where emp_id = 11; -- end test

[@SoftwareNuggets]

step 3 with cteTestHierarchy(emp_id, organizationNode, nodeAsString, ManagerID, HierLevel) as ( select th.emp_id, th.organizationNode, th.organizationNode.ToString() as NodeAsString, th.manager_id, 0 as HierLevel from testHierarchy th where th.manager_id is null union all select t.emp_id, t.organizationNode, t.organizationNode.ToString() as NodeAsString, t.manager_id, cte.HierLevel+1 from testHierarchy t join cteTestHierarchy cte ON t.organizationNode.GetAncestor(1) = cte.organizationNode ) select * from cteTestHierarchy --step 1 select th.emp_id, th.organizationNode, th.organizationNode.ToString() as NodeAsString, th.manager_id, 0 as HierLevel from testHierarchy th where th.manager_id is null -- step 2 (the recursive part) select * from testHierarchy where organizationNode.GetAncestor(1) = 0x -- output -- 10 0x58 -- 20 0x68 -- you have to think now -- look at the data -- which rows from the table have 0x as their GetAncestor(1) -- emp_id 10 and 20 -- step 3 select * from testHierarchy where organizationNode.GetAncestor(1) in (0x58, 0x68) -- step 4 select * from testHierarchy where organizationNode.GetAncestor(1) in (0x5AC0,0x5B40,0x5BC0,0x6AC0,0x6B40,0x6BC0) select th.emp_id, th.organizationNode, th.organizationNode.ToString() as NodeAsString, th.manager_id, 0 as HierLevel from testHierarchy th where th.manager_id is null union select t.emp_id, t.organizationNode, t.organizationNode.ToString() as NodeAsString, t.manager_id, 1 as HierLevel from testHierarchy t where organizationNode.GetAncestor(1) = 0x union select t.emp_id, t.organizationNode, t.organizationNode.ToString() as NodeAsString, t.manager_id, 1 as HierLevel from testHierarchy t where organizationNode.GetAncestor(1) in (0x58, 0x68) union select t.emp_id, t.organizationNode, t.organizationNode.ToString() as NodeAsString, t.manager_id, 1 as HierLevel from testHierarchy t where organizationNode.GetAncestor(1) in (0x5AC0,0x5B40,0x5BC0,0x6AC0,0x6B40,0x6BC0)

[@SoftwareNuggets]

step 4 select emp_id, organizationNode, organizationNode.ToString(), manager_id, organizationNode.GetLevel() from testHierarchy order by organizationNode.GetLevel(), manager_id -- new employee is hired -- employee 23 will be the boss declare @node hierarchyid; set @node = CAST('/2/3/' as hierarchyid); select @node.GetDescendant(null,null).ToString(); insert into testHierarchy(emp_id, organizationNode, manager_id) values (231, @node.GetDescendant(null,null), 23) select emp_id, organizationNode, organizationNode.ToString(), manager_id, organizationNode.GetLevel() from testHierarchy order by organizationNode.GetLevel(), manager_id declare @node hierarchyid; declare @child1 hierarchyid; set @node = CAST('/2/3/' as hierarchyid); set @child1 = CAST('/2/3/1/' as hierarchyid); select @node.GetDescendant(@child1, NULL).ToString(); insert into testHierarchy(emp_id, organizationNode, manager_id) values (232, @node.GetDescendant(@child1,null), 23) declare @node hierarchyid; declare @child1 hierarchyid; set @node = CAST('/2/3/' as hierarchyid); set @child1 = CAST('/2/3/2/' as hierarchyid); select @node.GetDescendant(@child1, NULL).ToString(); insert into testHierarchy(emp_id, organizationNode, manager_id) values (233, @node.GetDescendant(@child1,null), 23) -- insert between two child nodes declare @node hierarchyid; declare @child1 hierarchyid; declare @child2 hierarchyid; set @node = CAST('/2/3/' as hierarchyid); set @child1 = CAST('/2/3/1/' as hierarchyid); set @child2 = CAST('/2/3/2/' as hierarchyid); select @node.GetDescendant(@child1, @child2).ToString(); insert into testHierarchy(emp_id, organizationNode, manager_id) values (234, @node.GetDescendant(@child1,@child2), 23) -- let's take a look at the tree select emp_id, organizationNode, organizationNode.ToString(), manager_id, organizationNode.GetLevel() from testHierarchy order by organizationNode.GetLevel(), manager_id