#hierarchyid

SQL Server Hierarchical Query using the hierarchyid type

In this SQL minute we’ll look at how to create a SQL server hierarchical query using the hierarchyid data type.  This is a two-part article.  In part one you were introduced to the hierarchyid type.  In this article we dig deeper into the hierarchyid functions.

If you’re studying for the 70-761 exam, you’ll find there aren’t many intermediate SQL problems with explanations for you to solve. …

Use hierarchyid to query Hierarchical data

In this SQL minute we’ll look at how you can use hierarchyid to query hierarchy data.  This is a two-part article.  In this article we look at how to do one query to get portion of the hierarchy.  Next week, we will look to see how to query the entire tree.

If you’re studying for the 70-761 exam, you’ll find there aren’t many intermediate SQL problems with explanation for you to solve.  To…

In SQL Server 2008, Microsoft introduced the hierarchyid data type.  While an application is responsible for populating this field (i.e. it does not automatically generate a tree), the built-in functions provided with this data type make it a much better option than simply storing an objects parent ID and performing recursive joins to find its parent, grandparent, etc...  Many folks are not familiar with this data type so I thought I'd write a quick example.

In the example below, I've created a fake medical institution with a couple divisions and departments.  Navigating through the organization is a snap using the built-in hierarchyid functions.

-- First setup some example data: begin try drop table #structure end try begin catch end catch create table #structure ( structure_id hierarchyid, organization_nm varchar(100), organization_abbr varchar(10) ) insert into #structure(structure_id, organization_nm, organization_abbr) values ('/0/','Tavernier Medical Sciences','TMS'), ('/0/1/','Preventative Care','PC'), ('/0/1/1/','Snakeoil Group','SO'), ('/0/1/2/','Blood-Letting','BL'), ('/0/1/2/1/','Hall 1','H1'), ('/0/1/2/1/1/','Bed A','BA'), ('/0/1/2/2/','Hall 2','H2'), ('/0/2/','Surgery','SG'), ('/0/2/1/','Transplants','TR'), ('/0/2/1/1/','Brain','BR'), ('/0/2/1/2/','Wings','WN'), ('/0/2/2/','Elective','EL'), ('/0/2/2/1/','Cyclops Creation','CC'), ('/0/2/2/2/','Centaur Addition','CA') -- Example queries: -- Get everything related under our Surgery division: select *, s.structure_id.GetLevel() as lvl, s.structure_id.ToString() as readable from #structure s where s.structure_id.IsDescendantOf('/0/2/') = 1 order by s.structure_id; -- Get everything that has the Blood-Letting department as its parent: select *, s.structure_id.GetLevel() as lvl, s.structure_id.ToString() as readable from #structure s where s.structure_id.GetAncestor('1') = cast('/0/1/2/' as hierarchyid) order by s.structure_id; -- List everything and it's parent: select s.*, s2.organization_nm as parent from #structure s left outer join #structure s2 on s.structure_id.GetAncestor(1) = s2.structure_id order by s.structure_id; -- List everything and it's grandparent: select s.*, s2.organization_nm as two_levels_up from #structure s left outer join #structure s2 on s.structure_id.GetAncestor(2) = s2.structure_id order by s.structure_id; -- Divisions in TMS: select * from #structure s where s.structure_id.GetLevel() = 2 order by s.structure_id;

Example result set for first query: