hierarchyid

I’ve been doing some testing, and it appears you do not need a record with a root hierarchyid. For example, normally you would one root node (level 1) and multiple childen, but you can skip the root node, having no root records, just records that start at level 2: //table schema CREATE TABLE [Entity]( [ID] … Read more

The most commonly used approach would be a recursive Common Table Expression (CTE) WITH Ancestors(Id, [Name], AncestorId) AS ( SELECT Id, [Name], Id.GetAncestor(1) FROM dbo.HierarchyTable WHERE Name=”Joe Blow” — or whatever you need to select that node UNION ALL SELECT ht.Id, ht.[Name], ht.Id.GetAncestor(1) FROM dbo.HierarchyTable ht INNER JOIN Ancestors a ON ht.Id = a.AncestorId ) … Read more