Mistake: writing a new recursive CTE for every hierarchy request. A self-referenced employee table can handle simple queries, but deeper variations become difficult to maintain. SQL Server’s hierarchyid type stores each position in a hierarchy and supplies methods for traversing it. section 3.3 section 3.6
A hierarchyid path uses slash-delimited levels: / is the root, /1/ and /2/ identify children, and deeper paths identify descendants such as /1/2/1/. SQL Server stores these paths internally as bit strings and displays them as hexadecimal values unless ToString() is called. section 3.3
Use the type and its methods for common organizational queries:
- Add a nullable hierarchyid column, then populate paths from the root through each employee’s manager relationship. section 3.3
- Call ToString() to inspect readable paths, IsDescendantOf() to return direct and indirect reports, and GetAncestor() to find an employee’s managers. section 3.3
- Use GetLevel() when comparing employees at the same organizational depth, such as Amy Fry’s organizational siblings. section 3.3
Sanjay Gupta’s reports
Retrieve a manager’s hierarchyid, then filter employees with IsDescendantOf(@Manager) = 1 to return direct and indirect reports without writing a traversal CTE for that query. section 3.3
Figure 3.2 MagicChoc management org chart
Use the management org chart to relate slash-delimited hierarchy paths to positions in a concrete organization. section 3.3
