Excellent article; I have used all three types of tree representations before. In one project, we used a materialized path, and I kind of wish I'd thought to use varbinary instead of a hex string. However, I just tested using an index on a materialized path as a hex string using 100000 rows of sample data generated with BuildLargeEmployeeTable, and the results were perhaps not so surprising.

SELECT

ISNULL(EmployeeID, 0) AS EmployeeID,

ISNULL(ManagerID, 0) AS ManagerID,

ISNULL(CONVERT(varchar(8000), sortpath, 2), '') as SortPath

INTO Hierarchy2

FROM Hierarchy

ALTER TABLE Hierarchy2 ADD CONSTRAINT PK_Hierarchy2 PRIMARY KEY CLUSTERED (EmployeeID)

CREATE INDEX SortPath ON Hierarchy2 (SortPath)

SQL Server will use a string index on LIKE operations anchored at the begining, so this:

SELECT * FROM Hierarchy2 WHERE SortPath LIKE '0000000100000002000000130000001F0000003F00000058000000650000009600000935%'

ends up being more than three times more efficient than this:

SELECT * FROM Hierarchy2 WHERE SUBSTRING(SortPath, 65, 8) = '00000935'

Unfortunately, the index only supports the first 900 bytes of the SortPath. Also, since the Nested Sets provide the same benefits (fast ancestor / descendant access and sorting), and since the update to calculate them using the SortPath is going to touch every row anyway, going with the approach you've got looks best. 🙂

I guess since the rebuild is so fast, you also don't need to consider using double-precision floating-point numbers as the bowers (which makes updating the bowers in place a bit more manageable, since you can keep subdividing the ranges up to a certain point).

Keep up the great work!